Phpkb Query Optimization
Indexes !


Use InnoDB, not MyISAM.
Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)...


Something like:

SELECT  a.article_id AS article_id, article_title, article_content,
        article_status, article_date_time, 
      ( SELECT  SUM(article_hits)
            FROM  phpkb_article_visits
            WHERE  article_id = a.article_id 
      ) AS article_hits
    FROM  phpkb_articles AS a JOIN phpkb_relations AS r  ON r.article_id = a.article_id
    WHERE  article_show='yes'
      AND  article_status IN ('featured','approved')
      AND  article_expiry_date &gt;= now()
      AND  category_id=221
    GROUP BY  a.article_id
    ORDER BY  FIELD(article_status,'featured','approved'),
              article_title ASC;



Please qualify each field with the table that it belongs to.  (I am depending on all the unqualified article_* fields belonging to article_hits.)


4.

Indexes needed:

phpkb_article_visits:  (article_id, article_hits)
article_hits:  (category_id, article_show, article_expiry_date)
phpkb_relations:  (article_id)



Notice how I simplified the test for article_expiry_date.