Re: I need Optimizaton of a Query - DISTINCT

[Sumeet Shroff]

Thanks for your suggestion. It did really help..

i am using this now... and it is return the same results but with less speed

Original query is taking - Showing rows 0 - 19 (20 total, Query took 9.4397 sec)

The below query - Showing rows 0 - 19 (20 total, Query took 0.0175 sec)

But kindly check the query and let me know if this is ok... (check the where clause in the sub select)

----
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 (

SELECT *
FROM jos_content
WHERE
state = '1'
AND access <=0 AND
(
publish_up = '0000-00-00 00:00:00'
OR publish_up <= '2010-10-13 15:51:07'
)
AND (
publish_down = '0000-00-00 00:00:00'
OR publish_down >= '2010-10-13 15:51:07'
)
ORDER BY created DESC
LIMIT 20
) 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
)