List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 22 1999 11:44pm
Subject:Re: Why this index?
View as plain text  
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
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