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