MySQL Forums
Forum List  »  MyISAM

I need Optimizaton of a Query - DISTINCT
Posted by: Sumeet Shroff
Date: October 13, 2010 08:45PM


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 as userid, IFNULL(,a.catid) as catid, IFNULL(,a.sectionid) as secid, as id, a.*, a.introtext as itext, a.fulltext as mtext, AS author, u.usertype, as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":",, a.alias) ELSE END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":",, c.alias) ELSE END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON = a.created_by
LEFT JOIN `jos_categories` AS c on = a.catid
LEFT JOIN `jos_sections` AS s on = 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;

Options: ReplyQuote

Written By
I need Optimizaton of a Query - DISTINCT
October 13, 2010 08:45PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.