Seriously. Don't use count anymore if you want your product to be stable.

[athlonkmf]

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.

Code Select Expand

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  


[Tarique Sani]

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

[athlonkmf]

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'");

[Hein Traag]

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 ?

[athlonkmf]

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

[Tarique Sani]

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.

[athlonkmf]

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.

[Tarique Sani]

#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

[Rodinou]

"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

[cdrake]

i searched too and it doesnt help me either.

[julala]

Code Select Expand

/*
        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