optimization of left outer joins
Posted by:
Clive Cox
Date: December 16, 2005 09:25AM
I have a situation where I am using JDO (Java Data Objects) and it is producing SQL for a query with LEFT OUTER JOINs which create a very slow query.
A similar query but with standard joins runs much faster (0.1 secs compared to 4 secs!)
My question is, is this expected?
I have tables (representing java classes) in which the JDO query is essentially of the form
member.memberprivate.phone.phonenumber.fullnumber = '447841141711' &&
member.memberstate.state <> 5
The classes are joined together in the tables via primary keys
The JDO created query is (with names slightly simplified for easier reading):
SELECT * FROM `MEMBERX` `THIS` LEFT OUTER JOIN `MEMBERPRIVATEX` mp ON `THIS`.`MEMBERPRIVATEX` = mp.`JDOIDX`
LEFT OUTER JOIN `PHONEX` ph ON mp.`PHONEX` = ph.`JDOIDX` LEFT OUTER JOIN `PHONENUMBERX` pn ON ph.`PHONENUMBERX` = pn.`JDOIDX`
LEFT OUTER JOIN `MEMBERSTATEX` ms ON `THIS`.`MEMBERSTATEX` = ms.`JDOIDX` WHERE
pn.`FULLNUMBERX` = '447841141711' AND ms.`STATEX` <> 5
This fed to explain produces:
+----+-------------+-------+--------+------------------------+------------------------+---------+-----------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------+------------------------+---------+-----------------------------+-------+-------------+
| 1 | SIMPLE | pn | ref | PRIMARY,FULLNUMBERX | FULLNUMBERX | 256 | const | 1 | Using where |
| 1 | SIMPLE | ms | ALL | PRIMARY | NULL | NULL | NULL | 16565 | Using where |
| 1 | SIMPLE | THIS | ref | IDXMEMBERXMEMBERSTATEX | IDXMEMBERXMEMBERSTATEX | 9 | playtxt.ms.JDOIDX | 1 | Using where |
| 1 | SIMPLE | mp | eq_ref | PRIMARY | PRIMARY | 8 | playtxt.THIS.MEMBERPRIVATEX | 1 | |
| 1 | SIMPLE | ph | eq_ref | PRIMARY | PRIMARY | 8 | playtxt.mp.PHONEX | 1 | Using where |
+----+-------------+-------+--------+------------------------+------------------------+---------+-----------------------------+-------+-------------+
Note the 16565 rows that need to be read to get the state <> 5 from memberstate!
This query takes as expected 4 secs to run!
If I rewrite the SQL as:
select m.jdoidx from MEMBERX m,MEMBERPRIVATEX mp,PHONEX ph,PHONENUMBERX pn,MEMBERSTATEX ms where m.memberprivatex = mp.jdoidx and mp.phonex = ph.jdoidx and ph.phonenumberx = pn.jdoidx and m.memberstatex = ms.jdoidx and pn.fullnumberx = "447841141711" and ms.statex <> 5;
Explain says:
+----+-------------+-------+--------+-------------------------------------------------+--------------------------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------------+--------------------------+---------+------------------------+------+-------------+
| 1 | SIMPLE | pn | ref | PRIMARY,FULLNUMBERX | FULLNUMBERX | 256 | const | 1 | Using where |
| 1 | SIMPLE | ph | ref | PRIMARY,IDXPHONEXPHONENUMBERX | IDXPHONEXPHONENUMBERX | 9 | playtxt.pn.JDOIDX | 1 | Using where |
| 1 | SIMPLE | mp | ref | PRIMARY,IDXMEMBERPRIVATEXPHONEX | IDXMEMBERPRIVATEXPHONEX | 9 | playtxt.ph.JDOIDX | 1 | Using where |
| 1 | SIMPLE | m | ref | IDXMEMBERXMEMBERPRIVATEX,IDXMEMBERXMEMBERSTATEX | IDXMEMBERXMEMBERPRIVATEX | 9 | playtxt.mp.JDOIDX | 1 | Using where |
| 1 | SIMPLE | ms | eq_ref | PRIMARY | PRIMARY | 8 | playtxt.m.MEMBERSTATEX | 1 | Using where |
+----+-------------+-------+--------+-------------------------------------------------+--------------------------+---------+------------------------+------+-------------+
Whis is good and the query takes 0.1 secs to execute.
So, why can't mysql optimize the "left outer join" version the same way using the primary keys so it doesn't need to trawl through all MemberState rows to get the <> 5 condition?
Is there something fundamental as to why the left outer join version can't be optimized this way?
If there is a fundamental problem, then I can go back and complain more vigourously to the JDO vendor and ask how I can ensure JDO produces non-left outer join for my query!
Hope someone can help,
thanks,
Subject
Views
Written By
Posted
optimization of left outer joins
4772
December 16, 2005 09:25AM
2757
December 16, 2005 10:23AM
2273
December 19, 2005 11:42PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.