Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    May 2006
    Posts
    48
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Optimizing MySQL query

    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

    PHP Code:
    SELECT `id` , `nick` , `msg` , `uid` , `show_pic` , `time` , `ip` , `time_updated` , (
     
    SELECT COUNTc.msg_id )
    FROM `the_ansc
    WHERE c
    .msg_id d.id
    ) AS counter, (
     
    SELECT c.msg
    FROM 
    `the_ansc
    WHERE c
    .msg_id d.id
    ORDER BY 
    `timeDESC
    LIMIT 1
    ) AS lastmsg
    FROM 
    `the_datad
    ORDER BY 
    `time_updatedDESC
    LIMIT 26340 
    15 

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,548
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    What indexes do you have on the two tables?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •