MySQL Forums
Forum List  »  Optimizer & Parser

Re: Distinct/group by, order by and filesort
Posted by: Rick James
Date: February 25, 2010 10:39AM

The parens in this are confusing:
SELECT DISTINCT( article.id ) as TD, article.*
That is probably identical to
SELECT DISTINCT article.id as TD, article.*
That is, the DISTINCT is applied to the whole row, not just article.id.

Usually I admonish people to change subqueries into JOINs. This might be a case where going the other way is better.

SELECT a2.id as TD, a2.*, p.*
    FROM article a2,
         press_release p,
         ( SELECT id FROM article
                 WHERE published = 1
                   AND TORM_IS_CURRENT_REVISION = 1
         ) a1
    WHERE a2.id = a1.id
      AND a2.id = p.id
    ORDER BY a2.article_date;
Notes:
* The subquery will be "Using index" because of acx10.
* If id is UNIQUE, then no DISTINCT is needed. but it is not declared UNIQUE; why?
* There are a lot of redundant indexes. When one non-UNIQUE INDEX is a prefix of another, DROP the shorter one.
* TORM_IS_CURRENT_REVISION seems to be NULLable -- perhaps a mistake?
* I hope press_release has an index starting with (id).

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes (easier for me to read)
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Distinct/group by, order by and filesort
2492
February 25, 2010 10:39AM


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.