Re: Curious result: Indexing numbers vs. strings
Dr. Frank Ullrich wrote: Steve, what did explain tell you in either case? Good question Dr. Ullrich. mysql explain select rec_no,phone_no from White where phone_no=0636941; +---+--+---+--+-+--+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--++ | White | ALL | phone_no | NULL |NULL | NULL | 22160316 | where used | +---+--+---+--+-+--+--++ 1 row in set (0.01 sec) mysql explain select rec_no,phone_no from White where phone_no='0636941'; +---+--+---+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--++ | White | ref | phone_no | phone_no | 8 | const |1 | where used | +---+--+---+--+-+---+--++ 1 row in set (0.00 sec) So it's unable to use phone_no as a key, and therefore does a full scan. And then apparently proceeds to scan each row, converting phone_no to an integer to compare to the constant. Seems naive to me, since the datatype of the field is known, why not convert the constant instead? Then everything would work at least as well, and always faster. -steve -- Steve Rapaport World Citizen - 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: Curious result: Indexing numbers vs. strings
Anvar Hussain K.M. writes: 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. Good answer ! -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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: Curious result: Indexing numbers vs. strings
* Steve Rapaport Regarding integer/string conversions: select rec_no,phone_no from White where phone_no=0636941; versus select rec_no,phone_no from White where phone_no='0636941'; Seems naive to me, since the datatype of the field is known, why not convert the constant instead? Then everything would work at least as well, and always faster. ...except it would probably by converted to '636941', not '0636941'... -- Roger database, table, sql - 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: Curious result: Indexing numbers vs. strings
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
Re: Curious result: Indexing numbers vs. strings
Steve, what did explain tell you in either case? Regards, Frank. Steve Rapaport wrote: 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. Steve -- Steve Rapaport World Citizen - 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 -- Dr. Frank Ullrich, Netzwerkadministration Verlag Heinz Heise GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 - 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
