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
