List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 20 2002 6:10pm
Subject:Re: Index only sometimes used
View as plain text  
At 16:31 +0000 3/20/02, Steve Gardner wrote:
>Hi All
>
>Could someone explain to me why an index in a select I am doing is only
>sometimes used.
>The select I do is a complex one with multiple tables, and when it uses the
>index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50
>seconds.
>I have simplified it down and founed the place the problem is....see
>explains bellow:

For all the cases where the index is not being used, the "rows" value is
pretty high.  The optimizer has probably decided that the number of rows
relative to the total number of rows in the table is such that it'll
be just as fast to do a table scan as it would be to use the index.

>
>mysql> explain select * from mailstat where domain_id in(1,3,4,7,50,20,11);
>+----------+-------+---------------+------+---------+------+--------+-------
>-----+
>| table    | type  | possible_keys | key  | key_len | ref  | rows   | Extra
>|
>+----------+-------+---------------+------+---------+------+--------+-------
>-----+
>| mailstat | range | ind4          | ind4 |       4 | NULL | 239862 | where
>used |
>+----------+-------+---------------+------+---------+------+--------+-------
>-----+
>1 row in set (0.00 sec)
>*notice ind4 is used
>
>mysql> explain select * from mailstat where domain_id in(1,2);
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>| table    | type | possible_keys | key  | key_len | ref  | rows    | Extra
>|
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>| mailstat | ALL  | ind4          | NULL |    NULL | NULL | 2955648 | where
>used |
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>1 row in set (0.00 sec)
>*notice ind4 is NOT used
>
>mysql> explain select * from mailstat where domain_id in(1);
>+----------+-------+---------------+------+---------+------+--------+-------
>-----+
>| table    | type  | possible_keys | key  | key_len | ref  | rows   | Extra
>|
>+----------+-------+---------------+------+---------+------+--------+-------
>-----+
>| mailstat | range | ind4          | ind4 |       4 | NULL | 174922 | where
>used |
>+----------+-------+---------------+------+---------+------+--------+-------
>-----+
>1 row in set (0.00 sec)
>*notice ind4 is used
>
>mysql> explain select * from mailstat where domain_id in(2);
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>| table    | type | possible_keys | key  | key_len | ref  | rows    | Extra
>|
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>| mailstat | ALL  | ind4          | NULL |    NULL | NULL | 2955666 | where
>used |
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>1 row in set (0.00 sec)
>*notice ind4 is NOT used
>
>Basicaly, when I use '2' in the in() statement (and one or two other values
>from hundreds that do work), the index is not used.
>
>Initialy I thought that maybe the index was corupted, so I built a copy of
>the table, and inserted a handfull of rows, but the I got the same results,
>except for my new table 1 and 3 didnt work, but 2 did!.
>
>Out of interest I tried other indexes and found results like:
>
>mysql> explain select * from mailstat where  id<558693;
>+----------+-------+---------------+---------+---------+------+--------+----
>--------+
>| table    | type  | possible_keys | key     | key_len | ref  | rows   |
>Extra    |
>+----------+-------+---------------+---------+---------+------+--------+----
>--------+
>| mailstat | range | PRIMARY       | PRIMARY |       4 | NULL | 511375 |
>where used |
>+----------+-------+---------------+---------+---------+------+--------+----
>--------+
>1 row in set (0.00 sec)
>*primary key is used
>
>mysql> explain select * from mailstat where  id<558694;
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>| table    | type | possible_keys | key  | key_len | ref  | rows    | Extra
>|
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>| mailstat | ALL  | PRIMARY       | NULL |    NULL | NULL | 2956363 | where
>used |
>+----------+------+---------------+------+---------+------+---------+-------
>-----+
>1 row in set (0.00 sec)
>*primary key is NOT used
>
>If anyone could shed some light on this I would b most greatful :)
>
>Cheers, Steve

Thread
Index only sometimes usedSteve Gardner20 Mar
  • Re: Index only sometimes usedPaul DuBois20 Mar
  • Re: Index only sometimes usednigel wood20 Mar
  • Re: Index only sometimes usedGerald Clark20 Mar