By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,412 Members | 2,896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

Strange results when optimizing LEFT JOIN in MySQL

P: n/a
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.
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.