Roman, Tom:
> No no no ... this is even worse than before. Your big tables are
> batchdetail (d) and purc1 (p1). What you've got to do is arrange the
> computation so that those are trimmed to just the interesting records
> as
> soon as possible.
When joining disproportionally large tables, I've also had some success
with the following method:
SELECT b.batchdate, d.batchdetailid, t.bankno, d.trandate,
d.tranamount,
d.submitinterchange, d.authamount, d.authno, d.cardtypeid, d.mcccode,
m.name AS merchantname, c.cardtype, m.merchid,
p1.localtaxamount, p1.productidentifier, dr.avsresponse,
cr.checkoutdate, cr.noshowindicator, ck.checkingacctno,
ck.abaroutingno, ck.checkno
FROM tranheader t
JOIN batchheader b ON (t.tranheaderid = b.tranheaderid AND b.batchdate
> '2002-12-15')
JOIN merchants m ON (m.merchantid = b.merchantid AND mmerchid =
'701252267')
JOIN batchdetail d ON (d.batchid = b.batchid AND d.tranamount BETWEEN
500 and 700)
JOIN cardtype c ON d.cardtypeid = c.cardtypeid
LEFT JOIN purc1 p1 ON p1.batchdetailid = d.batchdetailid
LEFT JOIN direct dr ON dr.batchdetailid = d.batchdetailid
LEFT JOIN carrental cr ON cr.batchdetailid = d.batchdetailid
LEFT JOIN checks ck ON ck.batchdetailid = d.batchdetailid
WHERE t.clientid = 6
AND d.tranamount BETWEEN 500.0 AND 700.0
AND b.batchdate > '2002-12-15'
AND m.merchid = '701252267'
ORDER BY b.batchdate DESC
LIMIT 50
This could be re-arranged some, but I think you get the idea ... I've
been able, in some queries, to get the planner to use a better and
faster join strategy by repeating my WHERE conditions in the JOIN
criteria.
-Josh
