On Wed, 20 Mar 2002, Steve Gardner wrote: > Hi All > > Could someone explain to me why an index in a select I am doing is only > sometimes 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 <snip> > 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. <snip> > If anyone could shed some light on this I would b most greatful :) > > Cheers, Steve Check the manual section on the query optimiser,.I seem to remember that if MySQL has to check more that some percentage of a table (30% ?) the optimiser guesses it can search the entire table just as quickly. Someone please correct my if I'm wrong. Try 'select domain_id, count(*) from mailstat group by domain_id' to check the distribution of your data. Hope this helps, Nigel --------------------------------------------------------------------- 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
