Slow queries - what causes them
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?
# 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?
thanks for the prompt answer. Which file contains the query?
Edit: found it
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...)
|All times are GMT -5. The time now is 07:30 PM.|
Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0