List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 9 1999 3:28pm
Subject:Index Question
View as plain text  
>>>>> "Chris" == Chris  <chris@stripped> writes:

Chris> Hi, I have a question about the output of 'explain'. I have the following
Chris> table and keys:


Chris> My question is this, The manual states that:

Chris> MySQL can't use a partial index if the columns don't form a leftmost 
Chris> prefix of the index. Suppose you have the SELECT statements shown below: 

Chris> But when I do an explain like this:

mysql> explain select * from test where col1=1 and col2=2 and col3=3;
> +-------+-------+---------------+----------+---------+------+------+-------------------------+
Chris> | table | type  | possible_keys | key      | key_len | ref  | rows | Extra
Chris> |
> +-------+-------+---------------+----------+---------+------+------+-------------------------+
Chris> | test  | index | c1,c2,c3      | multidex |      16 | NULL |    3 | where
Chris> used; Using index |
> +-------+-------+---------------+----------+---------+------+------+-------------------------+
Chris> 1 row in set (0.00 sec)

Chris> It shows that the multiple column index was used, even though the columns
Chris> in the select statement did not form a leftmost prefix of the index... Am
Chris> I missing something about indices? Is this normal behavior when you have
Chris> both a multiple column index as well as single indices on each member of
Chris> the multiple index?

As you only refer columns that are part of an index, MySQL will solve 
the above query by scanning the index tree instead of scanning the
table. This should normally be faster...

Chris> I'm using the following rpm's on a 2.2.3 kernel based intel system:

Chris> MySQL-client-3.22.20a-1
Chris> MySQL-3.22.20a-1

Chris> Also, in the 'Extra' column, does 'Using index' mean the same as 'Index
Chris> Only'?

In this case yes.

Index QuestionChris9 Jun
  • Index QuestionMichael Widenius9 Jun