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

>
> >
> >>>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).
>

Thanks