Joomla slow Query

I have a joomla with near about 4k articles. Query are running very slow. below is one mysql slow query log.

# Query_time: 8.906729  Lock_time: 0.000470 Rows_sent: 4  Rows_examined: 5110986
SET timestamp=1315935450;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND( v.rating_sum / v.rating_count ) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM ql8wt_content AS a
LEFT JOIN ql8wt_categories AS c ON c.id = a.catid
LEFT JOIN ql8wt_users AS ua ON ua.id = a.created_by
LEFT JOIN ql8wt_users AS uam ON uam.id = a.modified_by
LEFT JOIN ql8wt_contact_details AS contact on contact.user_id = a.created_by
LEFT JOIN ql8wt_categories as parent ON parent.id = c.parent_id
LEFT JOIN ql8wt_content_rating AS v ON a.id = v.content_id
LEFT OUTE开发者_C百科R JOIN (SELECT cat.id as id FROM ql8wt_categories AS cat JOIN ql8wt_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
INNER JOIN ql8wt_content_frontpage AS fp ON fp.content_id = a.id
WHERE a.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-09-13 17:37:21') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-09-13 17:37:21')

How I can fix this?

Getting more CPU on hosting?

Increasing mysql memory (my hosting memory usages never goes more then 10%)? I had already increased mysql cache memory limit.

What should I try to fix it ?

Actually it's very difficult to say something with so little information, but at least you can rewrite your inefficient WHERE clause into this equivalent:

WHERE 
    a.access IN (1,1) AND 
    badcats.id IS NULL AND
    a.state = 1 AND
    a.publish_up <= '2011-09-13 17:37:21' AND 
    (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-09-13 17:37:21')

The Joomla model doesn't perform well with thousands of articles.

The models are very generic and meant to serve most uses; while it does work on larger sites, it definitely can use some improvement.

1. Use the system cache plugin
2. Make the cache time longer
3. Rewrite the slow queries (especially in the related articles module, latest articles etc) extracting only the information you require
4. Switch to fulltext search, and drop all the like '%$var%' statements
5. Use varnish for faster caching in front of Joomla

The first 4 actions brought our response times from 1+ seconds to 100 - 150ms. The last is not easy, but will let you get to 10 - 15ms per page.