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
