At 6:33 PM -0500 7/22/99, Steven Roussey wrote:
>mysql> create table tbl(a int not null,b int not null, c int not
>null,unique(a,b),index(a,c));
>mysql> insert into tbl a few records...
>mysql> show index from tbl;
>+-------+------------+----------+--------------+-------------+-----------+--
>-----------+----------+
>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
>Cardinality | Sub_part |
>+-------+------------+----------+--------------+-------------+-----------+--
>-----------+----------+
>| tbl | 0 | a | 1 | a | A |
>141318 | NULL |
>| tbl | 0 | a | 2 | b | A |
>141318 | NULL |
>| tbl | 1 | a_2 | 1 | a | A |
>NULL | NULL |
>| tbl | 1 | a_2 | 2 | c | A |
>NULL | NULL |
>+-------+------------+----------+--------------+-------------+-----------+--
>-----------+----------+
>4 rows in set (0.00 sec)
>
>mysql> explain select b from tbl where a=15950 and c=0;
>+-------+------+---------------+------+---------+---------+------+-------+
>| table | type | possible_keys | key | key_len | ref | rows | Extra |
>+-------+------+---------------+------+---------+---------+------+-------+
>| tbl | ref | a,a_2 | a_2 | 8 | ???,??? | 50 | |
>+-------+------+---------------+------+---------+---------+------+-------+
>1 row in set (0.09 sec)
>
>mysql> explain select b from tbl where a=12750 and c=0;
>+-------+------+---------------+------+---------+------+------+-------+
>| table | type | possible_keys | key | key_len | ref | rows | Extra |
>+-------+------+---------------+------+---------+------+------+-------+
>| tbl | ref | a,a_2 | a | 4 | ??? | 1413 | |
>+-------+------+---------------+------+---------+------+------+-------+
>1 row in set (0.15 sec)
>
>Why does mysql use different indexes for basicly the same query?
It will try to pick the index value that matches fewest records,
in order to have to examine fewer records in your tables.
How may records match the value 15950 and how many match 12750?
Is the count different?
--
Paul DuBois, paul@stripped
