newbe Q: how to optimize this query?

[Huaer.XC]

>From the following MySQL command:

EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name
= t1.name WHERE t1.id IN(123, 124);

which result is:
-------------------------------------------------------------------------------------
table type possible_key key key_len ref rows Extra
t1 const PK, name PK 4 const 10
t3 const PK PK 4 const 10
t2 ref PK PK 4 const 500
Using Where
-------------------------------------------------------------------------------------
where PK is PRIMARY, how can I optimize this query??

It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???

From the resulting EXPLAIN list, I thought I used only indexed PK on

the query, the PK is possibly t1.id or the composite of (id,name)???
how can I know if I had used indexes of both columns(id, name)??

Is there any ways that I should take to optimize this query??

Thank you very much for your input and hints.

Huaer

[Bill Karwin]

<Hu******@gmail .com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .

It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???

The MySQL optimizer tries to make the best choices. Even if you had another
index (and suppose MySQL could employ more than one index per table), it
might choose not to use it, if it estimated that using it would cost more
than it would benefit.

For instance, if the PK index is used to reduce the rows to a small number,
then it's very inexpensive to do the remaining JOIN with a full table-scan.
In such cases, it might actually be cost more than it saves to load another
index into memory. The MySQL optimizer tries to notice things like that and
may choose not to use an index even if one is available.

Here's an analogy: if you drive your car 20 miles to a gas station that has
cheaper prices, but you burn more gas than you save by doing so. Or you do
this when your tank is nearly full anyway, so the amount of cost benefit is
very little.

Regards,
Bill K.

[huaer]

Bill Karwin wrote:

<Hu******@gmail .com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .

It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???

The MySQL optimizer tries to make the best choices. Even if you had another
index (and suppose MySQL could employ more than one index per table), it
might choose not to use it, if it estimated that using it would cost more
than it would benefit.

For instance, if the PK index is used to reduce the rows to a small number,
then it's very inexpensive to do the remaining JOIN with a full table-scan.
In such cases, it might actually be cost more than it saves to load another
index into memory. The MySQL optimizer tries to notice things like that and
may choose not to use an index even if one is available.

Here's an analogy: if you drive your car 20 miles to a gas station that has
cheaper prices, but you burn more gas than you save by doing so. Or you do
this when your tank is nearly full anyway, so the amount of cost benefit is
very little.

Hi, Bill:
Thank you very much for your info. :-) but this is actually an
interview question, so I should have to find a way to optimize it
instead of letting MySQL do it..:-(.. would you please give me some
more hints especially about using EXPLAIN or composite indexes?? I had
read the documentation about optimizating SELECT on MySQL AB's website,
but still cannot make sure about the solutions..

Many thanks..
Huaer

[Bill Karwin]

"huaer" <Hu******@gmail .com> wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.com.. .

Thank you very much for your info. :-) but this is actually an
interview question, so I should have to find a way to optimize it
instead of letting MySQL do it..:-(.. would you please give me some
more hints especially about using EXPLAIN or composite indexes?? I had
read the documentation about optimizating SELECT on MySQL AB's website,
but still cannot make sure about the solutions..

If I do, do I get the job?

Bill K.

[noone]

huaer wrote:

Bill Karwin wrote:

<Hu******@gma il.com> wrote in message
news:11****** *************** *@v46g2000cwv.g ooglegroups.com ...

It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???

The MySQL optimizer tries to make the best choices. Even if you had another
index (and suppose MySQL could employ more than one index per table), it
might choose not to use it, if it estimated that using it would cost more
than it would benefit.

For instance, if the PK index is used to reduce the rows to a small number,
then it's very inexpensive to do the remaining JOIN with a full table-scan.
In such cases, it might actually be cost more than it saves to load another
index into memory. The MySQL optimizer tries to notice things like that and
may choose not to use an index even if one is available.

Here's an analogy: if you drive your car 20 miles to a gas station that has
cheaper prices, but you burn more gas than you save by doing so. Or you do
this when your tank is nearly full anyway, so the amount of cost benefit is
very little.

Hi, Bill:
Thank you very much for your info. :-) but this is actually an
interview question, so I should have to find a way to optimize it
instead of letting MySQL do it..:-(.. would you please give me some
more hints especially about using EXPLAIN or composite indexes?? I had
read the documentation about optimizating SELECT on MySQL AB's website,
but still cannot make sure about the solutions..

Many thanks..
Huaer

then are you really the right candidate for the job? :)