Given these two tables:
create table t1 (
id int unsigned auto_increment,
a int,
... [other fields]
primary key (id),
index aid (a,id)
) type=innodb;
create table t2 (
id int unsigned,
b int,
... [other fields]
index id (id),
index bid (b,id)
) type=innodb;
Using searches of the form:
select *
from t1, t2
where t1.id = t2.id
and t1.a = somevalue
and t2.b = somevalue
Now, let's say that the data is such that the driving table is t2
(order of tables with EXPLAIN is t2, t1).
Can MySQL take advantage of the "bid" index to retrieve the id
for the join out of the index rather than pulling the data row,
or is there no advantage to using "index bid (b,id)" over just
using "index bid (b)" for this query?
Similarly, can MySQL use "aid" for this query to satisfy both the
join and the "t1.a = somevalue" comparison together when t1 is
not the driving table like this? It appears to only want to use
the primary key for t1 for this query, which leads me to believe
that on non-driving tables the only index it can use is one to do
the join and that it can't use an index that could satisfy both
the join and another field comparison at the same time.
Thanks -keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
