>>>>> "Steven" == Steven Roussey <sroussey@stripped>
> writes:
mysql> create table tbl(a int not null,b int not null, c int not
Steven> null,unique(a,b),index(a,c));
mysql> insert into tbl a few records...
mysql> show index from tbl;
Steven> +-------+------------+----------+--------------+-------------+-----------+--
Steven> -----------+----------+
Steven> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Steven> Cardinality | Sub_part |
Steven> +-------+------------+----------+--------------+-------------+-----------+--
Steven> -----------+----------+
Steven> | tbl | 0 | a | 1 | a | A |
Steven> 141318 | NULL |
Steven> | tbl | 0 | a | 2 | b | A |
Steven> 141318 | NULL |
Steven> | tbl | 1 | a_2 | 1 | a | A |
Steven> NULL | NULL |
Steven> | tbl | 1 | a_2 | 2 | c | A |
Steven> NULL | NULL |
Steven> +-------+------------+----------+--------------+-------------+-----------+--
Steven> -----------+----------+
Steven> 4 rows in set (0.00 sec)
mysql> explain select b from tbl where a=15950 and c=0;
Steven> +-------+------+---------------+------+---------+---------+------+-------+
Steven> | table | type | possible_keys | key | key_len | ref | rows | Extra |
Steven> +-------+------+---------------+------+---------+---------+------+-------+
Steven> | tbl | ref | a,a_2 | a_2 | 8 | ???,??? | 50 | |
Steven> +-------+------+---------------+------+---------+---------+------+-------+
Steven> 1 row in set (0.09 sec)
mysql> explain select b from tbl where a=12750 and c=0;
Steven> +-------+------+---------------+------+---------+------+------+-------+
Steven> | table | type | possible_keys | key | key_len | ref | rows | Extra |
Steven> +-------+------+---------------+------+---------+------+------+-------+
Steven> | tbl | ref | a,a_2 | a | 4 | ??? | 1413 | |
Steven> +-------+------+---------------+------+---------+------+------+-------+
Steven> 1 row in set (0.15 sec)
Steven> Why does mysql use different indexes for basicly the same query?
MySQL does a quick 'range' query of the index tree to find out which
range should find least rows. The 'range' query is usually right
within 10 % which should ensure that it 'very often' finds the right
index to use.
For example, if c=0 for allmost all rows where a=12750, then the first
index is as good as the second index in this case:
To find you if MySQL does right in this case, do the following
queries:
select b from tbl where a=12750 and c=0;
select b from tbl where a=12750;
If the difference is not that big, then MySQL doesn't do anything
wrong here!
Regards,
Monty
