Dear Sirs,
I am experiencing strange results when trying to optimize a LEFT JOIN
on 3 tables using MySQL.
Given 3 tables A, B, C such as the following:
create table A (
uniqueId int not null default 0 auto_increment,
a1 varchar(64) not null default '',
a2 varchar(64) not null default '',
a3 varchar(64) not null default '',
...
primary key(uniqueId)
)
create table B (
uniqueId int not null default 0,
language varchar(2) not null default '',
b1 varchar(64) not null default '',
b2 varchar(64) not null default '',
b3 varchar(64) not null default '',
primary key (uniqueId, language)
)
create table C (
uniqueId int not null default 0,
language varchar(2) not null default '',
c1 varchar(64) not null default '',
c2 varchar(64) not null default '',
c3 varchar(64) not null default '',
primary key (uniqueId, language)
)
And given that for any value of the uniqueId in table A, there may
exist 0, 1, or more corresponding rows with the same uniqueId in
tables B and C.
I would like to query these tables using a LEFT JOIN for a given value
of the uniqueId. (Say, for uniqueId = 188)
This is easy enough using the following LEFT JOIN ~
SELECT A.*, B.*, C.* from
A LEFT JOIN B ON A.uniqueId = B.uniqueId
LEFT JOIN C on B.uniqueId = C.uniqueId
WHERE A.uniqueId = 188
AND B.lang = C.lang
However, this does not seem very efficient since ALL of the results
from the first join (A LEFT JOIN B) are used in the second join (on
table C) which is surely unnecessary since I know which row(s) from
the first join I really want to use (those with A.uniqueId = 188)
So, I tried selecting the specific record much earlier, with the
following
SELECT A.*, B.*, C.* from
A LEFT JOIN B ON (A.uniqueId = B.uniqueId AND A.uniqueId = 188)
LEFT JOIN C on B.uniqueId = C.uniqueId
WHERE B.lang = C.lang
But this seems to actually take longer. . how can that be ?
Even mysql's EXPLAIN seems to suggest that the former is faster:
This is the first LEFT JOIN query ~
+-------+-------+---------------+------------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------+-------+---------------+------------+---------+-------+------+------------+
| A | const | PRIMARY | PRIMARY | 4 | const | 1
| |
| B | ref | uniqueId | uniqueId | 4 | const | 1
| |
| C | ref | uniqueId | uniqueId | 4 | const | 1
| where used |
+-------+-------+---------------+------------+---------+-------+------+------------+
And this is when I try to optimize it ~
+-------+------+---------------+------------+---------+--------------+------+---
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+-------+------+---------------+------------+---------+--------------+------+---
| A | ALL | NULL | NULL | NULL | NULL |
9387 | |
| B | ref | uniqueId | uniqueId | 4 | A.uniqueId |
1 | |
| C | ref | uniqueId | uniqueId | 4 | A.uniqueId |
1 | where used |
+-------+------+---------------+------------+---------+--------------+------+------------+
I'm not very conversant with the EXPLAIN output but looking at the
Rows above, it seems that the first query is faster since it uses left
rows. I don't understand why the second (my 'optimized') query can not
use the keys either.
Thank you very much if anybody can shed light on this,
Soefara.