* Steve Rapaport > I just found this unexpected result, those who know how > indexing works might understand it but I don't, and it's > funny: > > I have a large phone listing with over 22 million records. > The phone number is a string (varchar 16). > > > There's an index for the first 8 chars of the phone number. > > Now note the response times when I forget the quotes: > > > mysql> select rec_no,phone_no from White where phone_no='0636941'; > +---------+----------+ > | rec_no | phone_no | > +---------+----------+ > | 1860796 | 0636941 | > +---------+----------+ > 1 row in set (0.06 sec) > > mysql> select rec_no,phone_no from White where phone_no=0636941; > +---------+----------+ > | rec_no | phone_no | > +---------+----------+ > | 1860796 | 0636941 | > +---------+----------+ > > 1 row in set (2 min 47.01 sec) > > I would have expected the second query to either work quickly, or to > fail altogether. > I am curious how it succeeded, but failed to use the indexing. I suspect > that any insight we get from this could help in optimizing db design and > queries in future.
I am guessing that the index is used, but that for each row the fetched varchar must be converted to integer, to compare it with the integer in the where clause. -- Roger --------------------------------------------------------------------- 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
