Optimize MySQL Query with multiple subqueries

Question

I have the following Query:

SELECT 
      p.id AS post_id,
      p.status,
      p.user_id AS post_user_id,
      ( SELECT  pl.id
            FROM  postlocks AS pl
            WHERE  pl.post_id = p.id ) AS postlock_id, 
      ( SELECT  COUNT(rn.id)
            FROM  renews AS rn
            WHERE  rn.post_id = p.id ) AS renew_count, 
      ( SELECT  rn.status
            FROM  renews AS rn
            WHERE  rn.post_id = p.id
            ORDER BY  rn.id DESC
            LIMIT  1                 ) AS last_renew_status, 
      ( SELECT  TIMESTAMPDIFF(SECOND, rn.timestamp, CURDATE())
            FROM  renews AS rn
            WHERE  rn.post_id = p.id
            ORDER BY  rn.id DESC
            LIMIT  1                 ) AS date_diff_from_renew,
      TIMESTAMPDIFF(SECOND, p.timestamp, CURDATE() ) AS date_diff_from_post
    FROM  posts AS p
    HAVING  (postlock_id IS NULL)
      AND  p.status='success'
      AND  ((date_diff_from_renew IS NOT NULL
                      AND  date_diff_from_renew > 172800
                      AND  last_renew_status='success')
              OR  (date_diff_from_renew IS NULL
                      AND  date_diff_from_post > 172800)
           )

It takes about 3 seconds to execute this query.

Here is the EXPLAIN:

+----+--------------------+-------+-------+---------------+---------+--    -------+----------+------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref      | rows | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+-------------+
|  1 | PRIMARY            | p     | ALL   | NULL          | NULL    |     NULL    | NULL     | 2988 |             |
|  5 | DEPENDENT SUBQUERY | rn    | index | post_id       | PRIMARY | 4           | NULL     |    1 | Using where |
|  4 | DEPENDENT SUBQUERY | rn    | index | post_id       | PRIMARY | 4           | NULL     |    1 | Using where |
|  3 | DEPENDENT SUBQUERY | rn    | ref   | post_id       | post_id | 4           | rto.p.id |    2 |             |
|  2 | DEPENDENT SUBQUERY | pl    | ALL   | NULL          | NULL    |     NULL    | NULL     |    1 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+-------------+

Is there any way to optimize this query, or do I need to do a total rewrite another way?

Thanks.

Answer 1

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id).

...for a start.

You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the optimizer does that. And the column needs to be indexed, otherwise you're scanning the table, as indicated by type = ALL in dependent subquery 2.