473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Strange results when optimizing LEFT JOIN in MySQL

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
0 1581

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: LRW | last post by:
I have a SQL string with a couple of JOINs that works perfectly in command prompt. I get results for all the fields selected just fine. But when I put it into a PHP page, I can't make variables(?)...
4
by: Ben | last post by:
Hi all, I'm trying to figure out how how complex map, filter and reduce work based on the following piece of code from http://www-106.ibm.com/developerworks/linux/library/l-prog.html : ...
0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
0
by: Apollo | last post by:
1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a...
9
by: Dave M | last post by:
All I've got a database that keeps track of sales of widgets. Each company that belongs to my organiztion is to report their widget sales or no sales every month. There are several different...
0
by: Soefara | last post by:
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...
0
by: Justin | last post by:
Hi. I decided to tidy up some of my queries, but I came across something that stumpt me: mysql> SELECT -> jobs.jobId, -> jobs.active, -> jobs.title, -> jobs.listed, -> ...
16
by: Justin Koivisto | last post by:
I am trying to create a query to use as a report record source. Below is what I want to do (this was tested and works with a MySQL web script): SELECT contacts.id, contacts.email,...
1
by: mamapossible | last post by:
Hi, I've spent hours trying to optimize this simple query: SELECT count(sites_jobs.id) as jobCount, sites_jobs_categories.frn_site_id, sites_jobs_categories.id, sites_jobs_categories.name,...
0
by: Luongo | last post by:
Hi, I'm working on a mysql-based survey program. On the user page I display the survey form, and on the admin page I display the results for each question. The problem, which I find puzzling, is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.