Hey, you know what? You're right! I'm an idiot. Thanks. :)
BD wrote: > Gabriel, > I have a sneaky suspicion your primary key is a CHAR or > VARCHAR, right? If so, your select statement is using an integer which > means it has to convert it for each record. If you put quotes around > the "30460203" it will run faster as in: > > select IndexField1,Field1,Field2 from table1 where IndexField1 = > "30460203"; > > Brent > > At 12:23 PM 3/18/2002, you wrote: > > >> BD wrote: >> >>> At 10:39 AM 3/18/2002, you wrote: >>> >>>> [snip] >>>> >>>> Field1 and Field2 are both unsigned integers >>>> IndexField1 is an unsigned integer and is the primary key >>>> >>>> select Field1,Field2 from table1 where IndexField1 = 345232; >>>> >>>> [snip] >>> >>> >>> >>> Gabriel, >>> The select you described should take very little time, like >>> around 0.01 seconds. But 9 seconds on a primary key? It appears that >>> it wasn't using the index. You may want to try the "Explain select >>> Field1,Field2 from table1 where IndexField1 = 345232;" to see if the >>> index was used. Also you may want to rebuild the index by dropping >>> it and creating it again in case the index was corrupted. You can >>> also run "myisamchk --analyze pathtodatabase/table to see if the >>> Explain changes on the query. >>> >>> Brent >> >> >> >> Ok, so I dropped the index (primary) and re-added it. I ran the >> myisamchk --analyze on the table also. After doing so it does not >> seem to be affected at all. I modified the query to select only the >> IndexField1 field and EXPLAIN showed that query to be using the index >> as it should: >> >> mysql> explain select IndexField1 from table1 where IndexField1 = 345232; >> >+----------+-------+---------------+---------+---------+------+-------+-------------------------+ >> | table | type | possible_keys | key | key_len | ref | rows >> | Extra | >> >+----------+-------+---------------+---------+---------+------+-------+-------------------------+ >> | table1 | index | PRIMARY | PRIMARY | 16 | NULL | 70029 >> | where used; Using index | >> >+----------+-------+---------------+---------+---------+------+-------+-------------------------+ >> >> Now, from what I understand, if I use the IndexField1 in the where >> clause (being the only part of the clause especially) it should use >> that index to find the correct record? But for some reason it only >> does that if the IndexField1 is the field I am selecting. If I added >> the Field1 and Field1 fields to the select query as show below, it >> still did not use the index: >> >> mysql> explain select IndexField1,Field1,Field2 from table1 where >> IndexField1 = 30460203; >> +----------+------+---------------+------+---------+------+-------+------------+ >> | table | type | possible_keys | key | key_len | ref | rows | >> Extra | >> +----------+------+---------------+------+---------+------+-------+------------+ >> | table1 | ALL | PRIMARY | NULL | NULL | NULL | 70029 | >> where used | >> +----------+------+---------------+------+---------+------+-------+------------+ >> >> >> Interestingly enough, using "LIMIT 1" in the query did speed it up, >> so it only took 0.01 seconds, however, using EXPLAIN with that query >> showed that it wasn't using the index at all. That really confuses me... >> >> >> - Gabriel Ricard > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.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
