I'm very disappointed to see that there is still usage of select counts in 1.4 of coppermine. I've made a topic on that before but it has been ignored.
If you use too much select count(*) you get a mysqlprocesslist like this.
Of course, for a small gallery there is no problem, but if you ever wants a gallery as big and popular as mine, then you better change the code. (and I only has 82.179 pictures)
And you know what's worse? this can be avoided all together. Just use a extra tablecolumn or even a new cache table to cache all those totals of pics/albums and you never have to do the selects again. Just update it with each
I'm currently working on my own project, making a frontend myself, but this is just a note for the current developpers. Think about performance for a bit.
stop proces 171664 asianfanatics localhost asianfanatics Query 675 Locked SELECT aid, count( pid ) AS pic_count, max( pid ) AS last_pid, max( ctime ) AS last_upload
FROM cpg11d_pic
stop proces 171769 asianfanatics localhost asianfanatics Query 618 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='353' OR keywords like '%Korean Female Jeon, Ji Hyun
stop proces 171772 asianfanatics localhost asianfanatics Query 618 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 171774 asianfanatics localhost asianfanatics Query 617 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 171778 asianfanatics localhost asianfanatics Query 615 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 171782 asianfanatics localhost asianfanatics Query 615 Locked SELECT * FROM cpg11d_pictures WHERE approved = 'YES' AND aid IN (1638,550,551,552,553,554,555,556,55
stop proces 171815 asianfanatics localhost asianfanatics Query 604 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 171818 asianfanatics localhost asianfanatics Query 603 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND hits > 0
stop proces 171819 asianfanatics localhost asianfanatics Query 603 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 171840 asianfanatics localhost asianfanatics Query 594 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 171885 asianfanatics localhost asianfanatics Query 573 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 171966 asianfanatics localhost asianfanatics Query 531 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND hits > 0
stop proces 171978 asianfanatics localhost asianfanatics Query 523 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 171980 asianfanatics localhost asianfanatics Query 523 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172002 asianfanatics localhost asianfanatics Query 511 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172014 asianfanatics localhost asianfanatics Query 502 Locked SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid,title, caption, ow
stop proces 172015 asianfanatics localhost asianfanatics Query 502 Locked SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid,title, caption, ow
stop proces 172022 asianfanatics localhost asianfanatics Query 498 Locked SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid,title, caption, ow
stop proces 172049 asianfanatics localhost asianfanatics Query 480 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172060 asianfanatics localhost asianfanatics Query 471 Locked SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid,title, caption, ow
stop proces 172071 asianfanatics localhost asianfanatics Query 465 Locked UPDATE cpg11d_pictures SET hits=hits+1 WHERE pid='86980'
stop proces 172074 asianfanatics localhost asianfanatics Query 463 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (1142,1143,1154,1155,1159,116
stop proces 172082 asianfanatics localhost asianfanatics Query 455 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='453' OR keywords like '%Korean Male Bae Yong Joon p
stop proces 172088 asianfanatics localhost asianfanatics Query 453 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172090 asianfanatics localhost asianfanatics Query 453 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172123 asianfanatics localhost asianfanatics Query 436 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172133 asianfanatics localhost asianfanatics Query 431 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1925' AND approved='YES'
stop proces 172151 asianfanatics localhost asianfanatics Query 424 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172171 asianfanatics localhost asianfanatics Query 415 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172187 asianfanatics localhost asianfanatics Query 408 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172190 asianfanatics localhost asianfanatics Query 405 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172226 asianfanatics localhost asianfanatics Query 384 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172235 asianfanatics localhost asianfanatics Query 380 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172259 asianfanatics localhost asianfanatics Query 370 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172261 asianfanatics localhost asianfanatics Query 370 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='453' OR keywords like '%Korean Male Bae Yong Joon p
stop proces 172264 asianfanatics localhost asianfanatics Query 368 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172272 asianfanatics localhost asianfanatics Query 364 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172278 asianfanatics localhost asianfanatics Query 363 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (14,4,5,6,9,10,11,12,13,15,16
stop proces 172307 asianfanatics localhost asianfanatics Query 346 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172308 asianfanatics localhost asianfanatics Query 346 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172319 asianfanatics localhost asianfanatics Query 341 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172351 asianfanatics localhost asianfanatics Query 328 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172353 asianfanatics localhost asianfanatics Query 327 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172359 asianfanatics localhost asianfanatics Query 323 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172361 asianfanatics localhost asianfanatics Query 322 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='353' OR keywords like '%Korean Female Jeon, Ji Hyun
stop proces 172366 asianfanatics localhost asianfanatics Query 319 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172368 asianfanatics localhost asianfanatics Query 318 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172370 asianfanatics localhost asianfanatics Query 317 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1829' AND approved='YES'
stop proces 172381 asianfanatics localhost asianfanatics Query 311 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='17' OR keywords like '%Chinese Female Joey Yung Cho-
stop proces 172382 asianfanatics localhost asianfanatics Query 311 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172388 asianfanatics localhost asianfanatics Query 308 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172420 asianfanatics localhost asianfanatics Query 291 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172422 asianfanatics localhost asianfanatics Query 289 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='453' OR keywords like '%Korean Male Bae Yong Joon p
stop proces 172423 asianfanatics localhost asianfanatics Query 289 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172446 asianfanatics localhost asianfanatics Query 283 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172452 asianfanatics localhost asianfanatics Query 280 Locked SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid,title, caption, ow
stop proces 172512 asianfanatics localhost asianfanatics Query 250 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (52,53,54,112,1595,1596,1597)
stop proces 172513 asianfanatics localhost asianfanatics Query 250 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='2' OR keywords like '%Taiwanese female Ruby Lin Sum-
stop proces 172514 asianfanatics localhost asianfanatics Query 249 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='453' OR keywords like '%Korean Male Bae Yong Joon p
stop proces 172549 asianfanatics localhost asianfanatics Query 230 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (52,53,54,112,1595,1596,1597)
stop proces 172564 asianfanatics localhost asianfanatics Query 224 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='1638' AND approved='YES'
stop proces 172572 asianfanatics localhost asianfanatics Query 219 Locked SELECT COUNT(*) from cpg11d_pictures WHERE approved = 'YES' AND aid IN (52,53,54,112,1595,1596,1597)
stop proces 172602 asianfanatics localhost asianfanatics Query 207 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172634 asianfanatics localhost asianfanatics Query 182 Locked SELECT COUNT(*) from cpg11d_pictures WHERE aid='2' OR keywords like '%Taiwanese female Ruby Lin Sum-
stop proces 172739 asianfanatics localhost asianfanatics Query 108 Locked SELECT * from cpg11d_pictures WHERE aid='131' OR keywords like '%Chinese Female Stephanie Cheng Yung
stop proces 172745 asianfanatics localhost asianfanatics Query 105 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172767 asianfanatics localhost asianfanatics Query 90 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
stop proces 172772 asianfanatics localhost asianfanatics Query 87 Locked SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg11d_pic
Yeah! Coppermine NG (next generation) will implement what you are talking about.... 1.x series could have indeed optimized the queries for count but unfortunately it never happened :(
OK,after just a quickscan through the functions.inc.php and change/disabling of a few queries, I managed to bring my server load from 25+ back to 0.8 (which is a good thing)
I've removed the useless counts. Comments, Total viewed, etc.
Also, removed the use of $keywords in queries during album views. (SERIOUSLY how much do you have to drink to do a 'like 'keywords' queries during each album or picture view??)
/*
if (!empty($CURRENT_ALBUM_KEYWORD)){
$keyword = "OR keywords like '%$CURRENT_ALBUM_KEYWORD%'";
} else */
$keyword = '';
Also, made the add_hit function low_priority
db_query("UPDATE LOW_PRIORITY {$CONFIG['TABLE_PICTURES']} SET hits=hits+1 WHERE pid='$pid'");
Sp you have changed the functions file so it still counts but keeps the serverload low as well or have you completely removed counting.
Would you mind sharing your work then so other big and popular galleries might benefit from it ?
no, i've removed several counts but have to leave the fatal ones. Like total pics, views, comments, etc were removed, but pics per albums total albums stayed. I'm planning to change this later.
But the thing that is most effective in reducing loads is getting rid of the use of $keywords in albumviewquereis
like %keywords% queries are needed if you want the one picture in many albums functionality and for stats atleast it is an admin settable option.... like I said NG will have a different way - and - no need to get abusive or derogatory - you are however excused if you were drunk while writting this.
Actually, there is no need for %like% queries, all you have to do is make a new table AlbumIDPicID which lists all the albums a picture is part of. And also, as far as I know, CPG1.3.2 does not have the pic in multialbum feature so there is no need to use that kind of query.
As for stats. you can't disable the total stats on index-page. And a select count of 80k with every start of the gallery is not a pretty sight...
And I wasn't drunk when writing this topic, but rather mad that my previous warning got ignored and seeing that 1.4 is still making the same mistake.
#1 1.3.2 does allow pic in multialbum - just that the frontend got left off
#2 1.4 has an option to turn off counting of linked files
#3 Warning at times is not enough where is the patch :)
"happy" to see I'm not the one who has this problem with queries (about 63000 pics for me ... and a lot of visitors) ... I have found this thread by searching a way to reduce queries ... but this post doesn't help me :(
i searched too and it doesnt help me either.
/*
if (!empty($CURRENT_ALBUM_KEYWORD)){
$keyword = "OR keywords like '%$CURRENT_ALBUM_KEYWORD%'";
} else */
$keyword = '';
Also, made the add_hit function low_priority
db_query("UPDATE LOW_PRIORITY {$CONFIG['TABLE_PICTURES']} SET hits=hits+1 WHERE pid='$pid'");
Tried doing what you suggest above to help load and I get a database error. Anything you might have missed out?
Thanks