Hi
today i received email from my hosting account saying that i need to tweak my query
Quote:
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
PHP Code:
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
Before you post, read our: 
