On Thu, 2003-01-16 at 03:40, Stephan Szabo wrote:
> > So here's the query, and another EXPLAIN ANALYZE to go with it
> > (executed after all setting changes). The same result columns and
> > JOINS are performed all day with variations on the WHERE clause; other
> > possible search columns are the ones that are indexed (see below).
> > The 4 tables that use LEFT JOIN only sometimes have matching records,
> > hence the OUTER join.
> >
> > EXPLAIN ANALYZE
> > 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
> > INNER JOIN batchheader b ON t.tranheaderid = b.tranheaderid
> > INNER JOIN merchants m ON m.merchantid = b.merchantid
> > INNER JOIN batchdetail d ON d.batchid = b.batchid
> > INNER 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
How much of data in d has tranamount BETWEEN 500.0 AND 700.0 ?
Do you have an index on d.tranamount ?
> > AND b.batchdate > '2002-12-15'
again - how much of b.batchdate > '2002-12-15' ?
is there an index
> > AND m.merchid = '701252267'
ditto
> > ORDER BY b.batchdate DESC
> > LIMIT 50
these two together make me think that perhaps
b.batchdate between '2003-12-12' and '2002-12-15'
could be better at making the optimiser see that reverse index scan on
b.batchdate would be the way to go.
> Well, you might get a little help by replace the from with
--
Hannu Krosing <hannu@tm.ee>