On 18 Jul 2006 20:01:44 -0700, howachen@gmail.com wrote:
>
> Jerry Stuckle ???
>
>> 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.
>>
>
> when joining a table, IF ONLY ONE index can be used (i.e. primary key),
> then add another index to the FK is useless, no matter the table size
> (in fact, it will affect the performance of upadating/inserting)
>
> i don't understand how index on FK will improve performane, IF ONLY ONE
> (i.e. PK) index can be used at a time

An index is REQUIRED on a foreign key. Whether it is used for *other*
operations is up to the optimizer. Which single index is to be used
during a query against a given table with many indexes will vary
depending on other circumstances (such as which other tables are joined
to the table we're considering, and how). Regardless of whether a query
is using an index, it must still be there.

--
82. I will not shoot at any of my enemies if they are standing in front of
the crucial support beam to a heavy, dangerous, unbalanced structure.
--Peter Anspach's list of things to do as an Evil Overlord