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
>
>
