Slow queries - what causes them
 

Hi,
I want to track down what causes this type of slow query (our s-q-log is full of these).

Which file exectute this kind of queries?
Thanks
Thomas

# Time: 060919 17:52:47
# User@Host: xxxxxxxxxxx @ xxxxxxx [10.0.0.2]
# Query_time: 12 Lock_time: 0 Rows_sent: 206685 Rows_examined: 413370
SELECT id,user,userid,cat,storecat,date,title,description,keywords,bigimage,width,height,filesize,views,medwidth,medheight,medsize,approved,rating,allowprint,numcom,lastposter FROM photos WHERE approved < 2 ORDER BY disporder,date DESC;

Possible spider on your site. There is nothing technically wrong with the query itself. it is only called in one place on the gallery. If you hit on the indexhead the ALL link and it shows all photos

Why out of couriosity do you have over 20000 unapproved photos?

Hi Chuck,
thanks for the prompt answer. Which file contains the query?
Edit: found it


I have no idea - I guess it was a former pp bug ;)

Thanks
Thomas

Query_time: 12 Lock_time: 0 Rows_sent: 206685 Rows_examined: 413370

I mean 200000. what is in your unapproved que? Maybe I am just reading it wrong

It is just a strange output from mysql - we only have 200K photos in total.
rows examined = 2*rows sent - why - I don't know.

I added a limit 1000 to the query which should avoid the slow query

yeah indeed strange output ;)

Yeah well you might want to change your wording for that template to say last 1000 not all

another one, which causes slow queries and table locking:
SELECT * FROM comments WHERE comment != '' ORDER BY date DESC;

from which file this is issued?
(please add filename (php_self) in future pp-versions as comment in sql - makes troubleshooting much easier)

It is in comshow.php

how did you fix this slow querying? We too are having performance issues on our site and losing valuable viewership and member visits due to it.

A quick solution is to add limit clauses to the respective queries...

how would one do that precisely?

find the query, add ad the end e.g. "limit 1000"
You should know what you are doing, because it might have strange sideeffects (e.g. if pp is using the select to build pagination or something like that...)