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

Click Here to see the explain screenshot

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.

INNER JOIN
article_related_categories rc ON a.id = rc.article_id and rc.category_id = 1

then remove rc.category_id = 1 from the where

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.