MySQL Forums
Forum List  »  Optimizer & Parser

Re: Range Join Performance
Posted by: Jay Pipes
Date: December 26, 2005 07:56PM

This part of your original query is not useful:
> ORDER BY
> O.ID,
> O.ARBBASE,
> D.ARBBASE

as you are simply getting a count of the rows, and don't care about sorting.

> The _arbrnge table cannot be directly joined to
> the _batch_load table because _arbrgne contains a
> range of values.

I don't see why not:

SELECT COUNT(DISTINCT origin.id)
FROM rateware._batch_load origin
INNER JOIN rateware._batch_load destination
ON origin.id = destination.id
INNER JOIN rateware._arbrnge arborigin
ON origin.ozip BETWEEN arborigin.zip_lo AND arborigin.zip_hi
INNER JOIN rateware._arbrnge arbdestination
ON destination.dzip BETWEEN arbdestination.zip_lo AND arbdestination.zip_hi
WHERE arborigin.arbbase != arbdestination.arbbase;

BTW, the != in the WHERE clause will prevent you from using an index on arbbase, so don't place an index on that column thinking it will speed things up, because it won't.

If you place indexes on _batch_load (id) (which I assume you already have...) and indexes on _batch_load (ozip) and _batch_load (dzip), you should be good to go. Additionally, you can create a covering index by placing an index on _arbrnge (zip_lo, zip_hi, arbbase).

Good luck.

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3927
November 23, 2005 08:47AM
2104
December 21, 2005 11:10PM
Re: Range Join Performance
2119
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.