On Thu, 26 Feb 2004, Keith Thompson wrote:
> 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?
Sure, it can do that.
>
> 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.
When I just created your test tables with no extra columns, explain
shows it didn't want to use the multicolumn index on the second
table (ie. "using index") unless I explicitly did a force index,
but then it did so just fine:
mysql> explain select * from t1, t2 force index(bid) where t1.id = t2.id and t1.a=
'xxx' and t2.b = 'yy' \G
*************************** 1. row ***************************
table: t1
type: ref
possible_keys: PRIMARY,aid
key: aid
key_len: 5
ref: const
rows: 1
Extra: Using where; Using index
*************************** 2. row ***************************
table: t2
type: ref
possible_keys: bid
key: bid
key_len: 10
ref: const,t1.id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)
It may well change its perspective and decide to use the index automatically
if I actually had more columns in the table, or had data in it, but I
don't know offhand if it is smart enough for that...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
