Hopefully this is simpler for someone here than me, I am struggling how to optimise database where a query orders from the result of a join on MySQL. Below:

  UNIX_TIMESTAMP(c.created) AS `created`,
  COUNT(cm.id) AS `comments_count`,
  `ph`.`file` AS `photo`
  `news` AS `n`
  INNER JOIN `contents` AS `c`
    ON c.id = n.content_id
  INNER JOIN `users` AS `u`
    ON u.id = c.author
  LEFT JOIN `comments` AS `cm`
    ON cm.hosting = c.id
    AND cm.type = "content"
    AND cm.deleted = 0
    AND cm.hidden = 0
  LEFT JOIN `photos` AS `ph`
    ON ph.id = n.photo_id
WHERE (main = 1)
  AND (c.deleted = 0)
  AND (c.approved = 1)
GROUP BY `n`.`id`
ORDER BY `c`.`sticky` DESC,
  `n`.`id` DESC

In this, if I remove the ORDER BY sticky, the query is near instant where as it takes about 6 seconds otherwise. I am not too sure what (if any) index I can put into place to help sort - I have tried indexing id and sticky, just sticky, and a bunch of shot in the dark combinations with no luck.

I have also tried index hinting however this doesn't appear to work with joined tables.

  • The reason the query is so fast without the order by is that the first 10 rows are projected. The order by is forcing MySQL to obtain all the rows, sort them, then show the first 10 sorted rows. In general, you will want to ensure the columns forming the heart of your join syntax are indexed. Use "explain select..." to see what execution plan the query optimizer is producing. – RMathis Mar 22 '17 at 20:25
  • consider indexing news.content_id, news.photo_id, contents.id, contents.author, users.id, comments.hosting, photos.id. – RMathis Mar 22 '17 at 20:30

"Explode (JOIN), Implode (GROUP BY)" is what I call it.

You can get rid of part of the explosion by changing

COUNT(cm.id) AS `comments_count`,


( SELECT COUNT(*) FROM comments WHERE ...
) AS comments_count,

and getting rid of the LEFT JOIN comments ON ....

If that does not speed it up enough, come back with the new SELECT, plus EXPLAIN SELECT, the table sizes, and SHOW CREATE TABLE. There may be other things we can do to speed it up. Is news:content in a 1:1 relationship?

  • +1. My thoughts exactly. Should be: "getting rid of the GROUP BY and LEFT JOIN ..." – ypercubeᵀᴹ Mar 23 '17 at 8:39

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.