MySQL Forums
Forum List  »  MyISAM

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

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
I need Optimizaton of a Query - DISTINCT
2550
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.