Slow queries - what causes them

dontom · September 19th, 2006, 12:41 PM

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;

Chuck S · September 19th, 2006, 01:55 PM

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?

dontom · September 19th, 2006, 02:14 PM

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

Quote:

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

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

Thanks
Thomas

Chuck S · September 19th, 2006, 02:31 PM

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

dontom · September 19th, 2006, 02:59 PM

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

Chuck S · September 19th, 2006, 03:23 PM

yeah indeed strange output

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

dontom · September 25th, 2006, 01:34 PM

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)

Chuck S · September 25th, 2006, 02:59 PM

It is in comshow.php

glowinggal · October 10th, 2006, 01:08 PM

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.

dontom · October 10th, 2006, 01:11 PM

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

glowinggal · November 9th, 2006, 09:55 PM

how would one do that precisely?

dontom · November 9th, 2006, 10:43 PM

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...)