MySQL Forums
Forum List  »  Optimizer & Parser

Range Join Performance
Posted by: Lionel Horn
Date: November 23, 2005 08:47AM

I have a query that executes in 1 second in Oracle but executes in 16 seconds in MySQL. The query performs a range "join" instead of an equijoin.

Here is the query:

--------- cut here --------------

SELECT COUNT(*)
FROM
(
SELECT
O.ID, O.ARBBASE ARBBASE_FROM, D.ARBBASE ARBBASE_TO, O.W, O.C
FROM
(
select
_batch_load.id ID, _batch_load.ozip O, _batch_load.W W, _batch_load.C C, _arbrnge.arbbase ARBBASE
from
rateware._batch_load,
rateware._arbrnge
where
_batch_load.ozip between _arbrnge.zip_lo and _arbrnge.zip_hi
group by
_batch_load.id, _batch_load.ozip, _batch_load.dzip, _batch_load.W, _batch_load.C, _arbrnge.arbbase
order by
_batch_load.id, _arbrnge.arbbase
) O,
(
select
_batch_load.id ID, _batch_load.dzip D, _batch_load.W W, _batch_load.C C, _arbrnge.arbbase ARBBASE
from
rateware._batch_load,
rateware._arbrnge
where
_batch_load.dzip between _arbrnge.zip_lo and _arbrnge.zip_hi
group by
_batch_load.id, _batch_load.ozip, _batch_load.dzip, _batch_load.W, _batch_load.C, _arbrnge.arbbase
order by
_batch_load.id, _arbrnge.arbbase
) D
WHERE
O.ID = D.ID
and o.arbbase != d.arbbase
ORDER BY
O.ID,
O.ARBBASE,
D.ARBBASE
) T

--------- cut here --------------

The _arbrnge table cannot be directly joined to the _batch_load table because _arbrgne contains a range of values. For each record in _batch_load, I have to find if it falls within a range defined in _arbrnge. This is why I used the BETWEEN clause.

Options: ReplyQuote


Subject
Views
Written By
Posted
Range Join Performance
3916
November 23, 2005 08:47AM
2097
December 21, 2005 11:10PM
2114
December 26, 2005 07:56PM


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.