2

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.

  • 1
    You may try moving the subqueries from SELECT clause to the FROM clause, joining the results. You might need to extend the subqueries with joins to apply some WHERE conditions sooner. – jkavalik May 30 '16 at 9:01
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.