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