MySQL Forums :: MyISAM :: I need Optimizaton of a Query - DISTINCT


Advanced Search

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 2501 Sumeet Shroff 10/13/2010 08:45PM
Re: I need Optimizaton of a Query - DISTINCT 1112 Rick James 10/16/2010 04:22PM
Re: I need Optimizaton of a Query - DISTINCT 1234 Sumeet Shroff 10/16/2010 06:29PM
Re: I need Optimizaton of a Query - DISTINCT 1097 Rick James 10/17/2010 11:15AM
Re: I need Optimizaton of a Query - DISTINCT 1175 Sumeet Shroff 10/17/2010 08:36PM
Re: I need Optimizaton of a Query - DISTINCT 1022 Sumeet Shroff 10/17/2010 08:50PM
Re: I need Optimizaton of a Query - DISTINCT 1155 Rick James 10/17/2010 10:51PM
Re: I need Optimizaton of a Query - DISTINCT 1080 Sumeet Shroff 10/18/2010 12:35AM


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.