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