Re: Creating tons of tables to support a query

On Mon, Sep 09, 2002 at 11:13:01AM -0400, S Dawalt wrote:
>
> Stephan Szabo said:
>
> >
> > On Sun, 8 Sep 2002, Jan Ploski wrote:
> >
> > > I am in particular wondering, why an index on message(sectionID,
> dateSent)
> > > does not make these queries comparably fast:
> > >
> > > select msgnum from message where
> > > sectionID = ? and
> > > dateSent > ?
> > > order by dateSent
> > > limit 1;
> >
> > I don't think that'll use an index on (sectionID, dateSent) for the sort
> > step. I think an index on (dateSent,sectionID) might be, however.
> >
>
> I know I've read this before on the list (probably several times). But
> either my skull is too thick or the topic too abstract; why is no index used
> for (sectionID, dateSent) but (dateSent, sectionID) does? They are the same
> columns, but just reversed. I don't see why that would make a difference.
> Is there some rule-of-thumb for determining when an index is used and when
> it isn't rather than trail and error using EXPLAIN?

Hmm, take out the order by. How long does it take then? How about trying:

select * from (select msgnum, datesent
from message where
sectionID = ? and
dateSent > ?) order by datesent limit 1;

maybe that will force the plan you want.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.