Re: Creating tons of tables to support a query

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?

Shane