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@svana.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.