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