MySQL Forums
Forum List  »  Optimizer & Parser

Re: Use of DISTINCT in INNER JOIN
Posted by: Rick James
Date: April 02, 2009 12:16AM

The absence of "Using index" does not mean that no index was used. Instead it means that the data was not consulted because all the necessary fields are in the index.

This indicates that an index was not used:
mysql> EXPLAIN SELECT ... \G
*************************** 1. row
           id: 1
  select_type: SIMPLE
        table: foo
         type: ALL      <--
possible_keys: msg
          key: NULL     <--
      key_len: NULL
          ref: NULL
         rows: 17499
        Extra: Using where

Another item... In InnoDB, the PRIMARY KEY is always "clustered" with the data. So, a table scan of the data and an index scan of the PRIMARY KEY do essentially the same thing, and have to walk through all the data (except maybe some BLOBs).

Some of your cases fall into this non-distinction between scanning the PK index and scanning the data. The EXPLAIN looks different, but the effect is not.

DISTINCT _may_ require an extra pass. Possibly the optimizer can realize that the data being SELECTed is all UNIQUE (that includes PRIMARY KEY). Possibly it has to make an extra pass over the data, either sorting it or using a hash table. This can be very much like GROUP BY. Sometimes, the extra sort pass is avoided by using the index, or folding in with the ORDER BY. In your cases, DISTINCT probably needed the extra pass.

Options: ReplyQuote


Subject
Views
Written By
Posted
66639
March 24, 2009 12:07PM
10887
March 24, 2009 02:00PM
6723
March 25, 2009 07:01PM
6429
March 27, 2009 05:20AM
5632
March 27, 2009 10:01AM
6602
March 28, 2009 02:02AM
4504
March 28, 2009 03:53PM
5694
April 01, 2009 03:36AM
Re: Use of DISTINCT in INNER JOIN
5967
April 02, 2009 12:16AM
5439
April 02, 2009 06:29AM


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.