Hi,

We always have query in the following form...

e.g.

SELECT * FROM a,b where a.id = b.fid

Should we add index to both a.id and b.fid?

Thanks...

howachen (AT) gmail (DOT) com wrote:
If a.id and b.fid are primary key and foreign key, respectively, they
already have indexes.

If not, then yes, creating indexes would be good. Do you know how to
use the EXPLAIN analyzer to tell you what indexes are being used by a
given query?

See http://dev.mysql.com/doc/refman/5.0/en/explain.html

Regards,
Bill K.

Bill Karwin 寫道：

1. i am using MyISAM, not InnoDB, so no foreign key.

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?

howachen (AT) gmail (DOT) com wrote:
Aha. Then you should create an index explicitly on b.fid.

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.

Regards,
Bill K.

Bill Karwin 寫道：

i heard that index on foreign key is needed only if you have foreign
key, otherwise, there is no need to, is it true?

what does it mean?

howachen (AT) gmail (DOT) com wrote:
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.


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


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

Jerry Stuckle 寫道：

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

Thanks

Peter H. Coffin 寫道：

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

howachen (AT) gmail (DOT) com wrote:
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.

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.

Bill Karwin 寫道：

thanks for your reply first...but the point is:

1. when joining two tables as above, only one index is used in the
whole query execution

my question is : why we need to create additional index on FK if MySQL
only can either use index PK or FK? why not force them to use PK (a.id)
and drop the index on b.fid (so as to save space and improve
insert/update performance)

thanks...