...

View Full Version : Optimizing MySQL query



Eben
01-21-2013, 04:24 PM
Hi
today i received email from my hosting account saying that i need to tweak my query


Problematic query:
SELECT `id`,`nick`,`msg`,`uid`,`show_pic`,`time`,`ip`,`time_updated`, (SELECT COUNT(c.msg_id) FROM `the_ans` c where c.msg_id = d.id) AS counter, (SELECT c.msg FROM `the_ans` c WHERE c.msg_id =d.id ORDER BY `time` DESC LIMIT 1) as lastmsg FROM `the_data` d ORDER BY `time_updated` DESC LIMIT 26340 ,15

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY d ALL 34309 Using filesort
3 DEPENDENT SUBQUERY c ALL 43659 Using where; Using filesort
2 DEPENDENT SUBQUERY c ALL 43659 Using where

This query examines 65,396,669,012,829 rows, which is unacceptable in shared hosting

tbh, i don't understand their explanation..
what the query actually does is to get 15 posts order by time updated,
for each post i grab the latest comment,
count all commnts for each post.

posts table - 'the_data'
commnts table = 'the_ans'

i'm not a mysql guru and i don't know how to improve this query
any help will be appreciated
thx

the query


SELECT `id` , `nick` , `msg` , `uid` , `show_pic` , `time` , `ip` , `time_updated` , (

SELECT COUNT( c.msg_id )
FROM `the_ans` c
WHERE c.msg_id = d.id
) AS counter, (

SELECT c.msg
FROM `the_ans` c
WHERE c.msg_id = d.id
ORDER BY `time` DESC
LIMIT 1
) AS lastmsg
FROM `the_data` d
ORDER BY `time_updated` DESC
LIMIT 26340 , 15

Old Pedant
01-21-2013, 09:09 PM
What indexes do you have on the two tables?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum