List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 1 1999 11:35pm
Subject:Why this index?
View as plain text  
>>>>> "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

Thread
Why this index?Steven Roussey23 Jul
  • Re: Why this index?Paul DuBois23 Jul
    • RE: Why this index?Steven Roussey23 Jul
      • Re: Why this index?Thimble Smith23 Jul
        • RE: Why this index?Steven Roussey23 Jul
      • RE: Why this index?Michael Widenius2 Aug
  • Schema GeneratorBradley Glonka23 Jul
    • Re: Schema GeneratorMicheal Mc Evoy23 Jul
    • Re: Schema GeneratorSasha Pachev23 Jul
  • Why this index?sinisa23 Jul
    • RE: Why this index?Steven Roussey23 Jul
  • Why this index?Michael Widenius2 Aug