Re: slow query for MIN function

"Kevin" <kevin.zahn_at_usbank.com> wrote in message news:ea1e2fb3.0307160435.2f36d30c_at_posting.google.com...
> I am trying to run the following query:
>
> Select min(tran_date)
> from Sales
> WHERE (Card1 = '123' OR Card2 = '123' OR Card3 = '123' )
> and product = 1
>
> It takes several minutes to return the tran_date. However, if I do a
> "Select *" and add in the where clause "and tran_date = "07-Jul-2003"
> it starts returning rows almost immediately.
>
> I have an index on
>
> 3 composite indexes:
> Card1 + tran_date + product
> Card2 + tran_date + product
> Card3 + tran_date + product
>
> The explain plan on the min(tran_date) query and the select * both use
> all three indexes.
>
>
> Why does the min(tran_date) query run so slowly?
>
> Thanks
>
> Kevin

You mentioned the explain plan but didn't include it.

the difference in your SELECT statements is that, although both use the indexes, the inclusion of tran_date in the query will cause a better use of the indexes in question. The original can only use the Card1/2/3 parts of the indexes. introducing tran_date, and already having 'product' in use brings the rest of the indexes into play. You should expect better/faster results in that instance.

Try changing the indexes to be in the order Card1/2/3 + product + tran_date. That way, your original query will use 2/3 of the inded capability. Also try product + Card1/2/3 + tran_date to see if that helps. Depends whcih reduces the result down fastest. Received on Wed Jul 16 2003 - 08:02:57 CDT