List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:March 20 2002 6:29pm
Subject:Re: Index only sometimes used
View as plain text  
If more than a third of the rows would be selected, it is faster not to 
use the index.
Over 2 million records match the '2'.
How many are there total?

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:
>
>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
>
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread103615@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


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