mysql not using indexes if select * where a <> 5
Natalino, Thursday, February 21, 2002, 1:46:11 PM, you wrote: NP> Hi, NP> I found that mysql doesn't used indexes if there is a <> in the where NP> statement... NP> for example NP> mysql> explain select * from testtable where id <> 5; NP> +-+--+---+--+-+--+---++ NP> | table | type | possible_keys | key | key_len | ref | rows | Extra | NP> +-+--+---+--+-+--+---++ NP> | testtable | ALL | NULL | NULL |NULL | NULL | 13505 | where NP> used | NP> +-+--+---+--+-+--+---++ NP> 1 row in set (0.00 sec) NP> please note that id is the primary key for the table... It is not efficient in this case. You can read in the manual how MySQL uses indexes: http://www.mysql.com/doc/M/y/MySQL_indexes.html NP> any ideas on how to optimize this type of queries ? NP> Nat -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - 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
Re: mysql not using indexes if select * where a <> 5
On Thursday, 21. February 2002 12:46, Natalino Picone wrote: > Hi, > I found that mysql doesn't used indexes if there is a <> in the where > statement... > for example > mysql> explain select * from testtable where id <> 5; It would only make sense to use an index, when you want to select the id field (SELECT id from testtable where id <> 5). Otherwise a table scan would be faster. Regards George - 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
mysql not using indexes if select * where a <> 5
Hi, I found that mysql doesn't used indexes if there is a <> in the where statement... for example mysql> explain select * from testtable where id <> 5; +-+--+---+--+-+--+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+---++ | testtable | ALL | NULL | NULL |NULL | NULL | 13505 | where used | +-+--+---+--+-+--+---++ 1 row in set (0.00 sec) please note that id is the primary key for the table... any ideas on how to optimize this type of queries ? Nat -- Natalino Picone - [EMAIL PROTECTED] -- It's a horrible thing to be on top of the world and then to lose it and try to get it back. It's a whole lot harder the second time. -- - 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
