howachen@gmail.com wrote:
> Bill Karwin 寫道：
>
>
>>howachen@gmail.com wrote:
>>
>>>1. i am using MyISAM, not InnoDB, so no foreign key.
>>
>>Aha. Then you should create an index explicitly on b.fid.
>
>
> i heard that index on foreign key is needed only if you have foreign
> key, otherwise, there is no need to, is it true?
>

It's only REQUIRED if you have a foreign key. Whether it's NEEDED or
not depends on the database, number of records in the database, queries
made to the database, how much performance hit you can stand and about
50 other variables.

But yes, usually an index on a foreign key can help performance, often
significantly for big tables.


>
>>>2. if both indexes are added, under explain statement, i see only one
>>>index is used at a time. the optimizer seems will select the best index
>>>to use?
>>
>>MySQL may use one index per table in a query. But the optimizer may
>>judge in some cases, that reading the index into memory is more
>>expensive than just reading the rows of data.
>>
>
>
> what does it mean?
>

It means the optimizer decided it was faster to read the data directly
from the table than to read in the index then access the table through
the pointers in the index. This most often occurs when you only have a
few rows in a table (but a table scan can also occur for other reason).

>
>>Regards,
>>Bill K.
>
>


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================