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 <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
