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