List:General Discussion« Previous MessageNext Message »
From:Chris Date:June 9 1999 12:22pm
Subject:Index Question
View as plain text  
Hi, I have a question about the output of 'explain'. I have the following
table and keys:

mysql> describe test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col0  | int(11) |      | MUL | 0       |       |
| col1  | int(11) |      | MUL | 0       |       |
| col2  | int(11) |      | MUL | 0       |       |
| col3  | int(11) |      | MUL | 0       |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> show keys from test;
...+----------+--------------+-------------+...
...| Key_name | Seq_in_index | Column_name |...
...+----------+--------------+-------------+...
...| multidex |            1 | col0        |...
...| multidex |            2 | col1        |...
...| multidex |            3 | col2        |...
...| multidex |            4 | col3        |...
...| c0       |            1 | col0        |...
...| c1       |            1 | col1        |...
...| c2       |            1 | col2        |...
...| c3       |            1 | col3        |...
...+----------+--------------+-------------+...
8 rows in set (0.00 sec)

The table contains this data:

mysql> select * from test;
+------+------+------+------+
| col0 | col1 | col2 | col3 |
+------+------+------+------+
|    0 |    1 |    2 |    3 |
|    1 |    2 |    3 |    4 |
|    2 |    3 |    4 |    5 |
+------+------+------+------+
3 rows in set (0.00 sec)

My question is this, The manual states that:

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

But when I do an explain like this:

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

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

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

MySQL-client-3.22.20a-1
MySQL-3.22.20a-1

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

Thanks!

--Chris

Thread
Index QuestionChris9 Jun
  • Index QuestionMichael Widenius9 Jun