Hi, Since the equality test is for a number, the phone_no field of every row of the table is converted into a number first and tested for the equality. This makes it impossible to use the character index and so forces the full table scan.
If it were using the index then, I think, it cannot find the intended records . This is because if it were the case, the number 0636941 which is in fact 636941 should be first conveted to string '636941' and the query executed and that is not the logical one to do. Anvar. At 09:46 PM 10/01/2002 +0100, you wrote: >* 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 --------------------------------------------------------------------- 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
