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:
SELECT `n`.*, `c`.`author`, `c`.`title`, `c`.`body`, UNIX_TIMESTAMP(c.created) AS `created`, `c`.`commenting`, `c`.`deleted`, `c`.`sticky`, `u`.`screenName`, COUNT(cm.id) AS `comments_count`, `ph`.`file` AS `photo` FROM `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 LIMIT 10;
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.