I need Optimizaton of a Query - DISTINCT
Hi
I have this query... this query takes 6 section to 18 secs....because we have huge table of 150 mb and 40,000 records....
But we take out the DISTINCT it takes only 0.6 seconds...
Can anyone optimize this query (removing DISTINCT) and still perform the same function...?
(I will pay for such request)
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, IFNULL(s.id,a.sectionid) as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, c.name as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
LEFT JOIN `jos_sections` AS s on s.id = c.section
WHERE a.state='1'
AND a.access <= 0
AND (c.access <= 0 OR a.sectionid = 0 )
AND (s.access <= 0 OR a.sectionid = 0 )
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2010-10-13 15:51:07')
AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2010-10-13 15:51:07')
ORDER BY a.created DESC LIMIT 20;