title: MySQL optimization query with subqueries
body: <p>Today i received email from my hosting account saying that i need to tweak my query:</p>
  
  <pre><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
  </code></pre>
  
  <p>EXPLAIN:</p>
  
  <pre><code>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
  </code></pre>
  
  <blockquote>
    <p>This query examines 65,396,669,012,829 rows, which is unacceptable in shared hosting.</p>
  </blockquote>
  
  <p>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 comments for each post.</p>
  
  <pre><code>posts table - 'the_data'
  
  comments table = 'the_ans'
  </code></pre>
  
  <p>i'm not a mysql guru and i don't know how to improve this query
  any help will be appreciated</p>
  
  <p>thx</p>
  
  <p>the query </p>
  
  <pre><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 
  </code></pre>
  
  <p>this is the results structure</p>
  
  <pre><code>id| nick  | msg  | uid   | show_pick | time      | ip |time_updated|counter|lastmsg
    |       |      |       |           |           |    |            |       |
  7 | jqman | hello| 10074 |   0       |2013-21-01 | 12 |2013-21-01  | 55    |blah bl
  </code></pre>