I have the following query which joins 5 related tables to get the desired result set of 10 rows, I did my best to solve the issue by adding indexes and re-writing the query in many different ways but I ended up either unexpected result or a very slow query.
HERE IS THE QUERY
SELECT a.*, c.id as category_id, c.title as catname, CONCAT(u.fname, ' ', u.lname) as username, DATE_FORMAT(a.created, '%W %M %d, %Y - %T') as long_date, DATE_FORMAT(a.created, '%d/%m/%Y - %T') as short_date, (SELECT COUNT(article_id) FROM comment WHERE article_id = a.id) as totalcomments, YEAR(a.created) as year, MONTH(a.created) as month, DAY(a.created) as day FROM article as a INNER JOIN article_related_categories rc ON a.id = rc.article_id LEFT JOIN category as c ON c.id = rc.category_id LEFT JOIN user as u ON u.id = a.user_id WHERE rc.category_id = 1 AND a.created <= NOW() AND (a.expire = '0000-00-00 00:00:00' OR a.expire >= NOW()) AND a.published IS NOT NULL ORDER BY a.created DESC LIMIT 0 , 10
Currently there are over 13,000 rows in the article table and a rapid growth is expected.
The trouble is, this query can take a significant amount of time to execute and it takes about 3-4 seconds. I suspect that the INNER JION causes most of the issue, but I thought I would ask here if anyone had any ideas for improving the performance of this query.