From: Dan Nelson
Date: December 1 2005 5:55pm
Subject: Re: Index merge optimization with joined tables?
List-Archive: http://lists.mysql.com/mysql/192399
Message-Id: <20051201175507.GA41231@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Dec 01), Kayra Otaner said:
> Is MySQL 5.0's Index merge optimization is also available to multiple
> table joins? When I do explain on 3 table joins, only one key per
> table is used :
> 
> SELECT R.*  FROM L, M, R
> WHERE
> L.key1  = 1
> AND    L.key2 = M.key2
> AND    M.key1 = R.key1
> 
> R table has 10 rows
> M table has 24 million rows
> L table has 9 million rows
> 
> Explain plan only shows ref & eq_ref type optimization is being used.
> M table has 2 key fields used in the joins and only one is used.
> Tables are InnoDB, total size of the tables is around 9G. I'm using
> MySQL max 5.0.16

Your WHERE clause may refer to two indexed fields for M, but an index
on M.key1 isn't going to get used anyway, because there's no need.

Here's a plaintext explain plan for what mysql will probably do:

Mysql will use the L.key1 index to find all the matching rows in L. For
each matching row, it will fetch the L.key2 value from the table and
use the M.key2 index to find the matching rows in M.  For each of those
matching rows, it will fetch the M.key1 value from the table and use
the R.key1 index to find all the matching rows in R.  For each of those
matching rows, it will fetch whatever fields you have selected from R. 
Whew!

An index on M.key1 isn't needed since you're not looking up specific
records in M based on key1.  You're looking for all the M.key1 fields
that correspond to records that match your M.key2 value.  If you need
that query to run faster (and are willing to take the space and
performance cost of maintaining more indexes), creating multi-column
indexes on L.(key1,key2) and M.(key2,key1) will let mysql use the 2nd
column of the index to directly access the other field data it needs
and avoid having to look up table records.

-- 
	Dan Nelson
	dnelson@stripped