howachen@gmail.com wrote:
>>> i don't understand how index on FK will improve performane, IF ONLY ONE
>>> (i.e. PK) index can be used at a time

MySQL can use one index per table. Your example named table "a" and
table "b". So it may use the primary key index from table "a" and the
foreign key index from table "b".

That's one index per table. If you have two tables, it may use two indexes.

> this is apply to InnoDB or other DB such as Oracle, not apply to MySQL
> ISAM table

We're not talking about Oracle.

MySQL has a limitation that it uses one index per table. This is true
whether you use InnoDB or MyISAM.

Actually, there are cases in MySQL 5.0 where it can merge indexes, and
so it uses more than one index per table. But only for certain types of
queries. Read the docs about merge indexes:
http://dev.mysql.com/doc/refman/5.0/...imization.html

Regards,
Bill K.