SEO

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@gmail.com wrote:
> SELECT * FROM a,b where a.id = b.fid
>
> Should we add index to both a.id and b.fid?

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 寫道：

> howachen@gmail.com wrote:
> > SELECT * FROM a,b where a.id = b.fid
> >
> > Should we add index to both a.id and b.fid?
>
> 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.

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

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

Regards,
Bill K.

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?

>
> > 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?

> Regards,
> Bill K.

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
==================

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

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

Peter H. Coffin 寫道：

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

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

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.