Which index to hit first?

[rloef]

I have a multi-million row table with three indexes in MySQL-5.0.15.
These indexes have the following number of distinct values:

date 415

block 100000

scan 45

If I'm doing a query on this table in what order should I hit these
indexes? The largest first, to get rid of the most possibilities right
away? Or the smallest first, because it has the fewest number of rows
to scan to make the cut? At some level both seem to make sense, but I'm
doing smallest to largest. Is this the most efficient? Or is this a
case of just letting the query optimizer choose? I don't yet have a
compound index on these fields.

[Gordon Burditt]

>I have a multi-million row table with three indexes in MySQL-5.0.15.

These indexes have the following number of distinct values:

date 415

block 100000

scan 45

If I'm doing a query on this table in what order should I hit these
indexes? The largest first, to get rid of the most possibilities right
away? Or the smallest first, because it has the fewest number of rows
to scan to make the cut?
You can't specify an order, so it doesn't make sense to talk about
"which index to hit first".
At some level both seem to make sense, but I'm
doing smallest to largest. Is this the most efficient? Or is this a
case of just letting the query optimizer choose?
As far as I know, MySQL does not use more than one index (including
a compound index, which is why compound indexes are useful) for
a given table lookup (in the case of joins, one per join). The
query optmizer will choose something like the index with the most
distinct values, or the index that retrieves the fewest records.

There are some features to force using a specific index. I recommend
you ignore them.

Think of looking up things in a phone book by first name, last name,
and address. You've got two phone books, one indexed by last name,
one indexed by first name. Do you look up the last name in one
index, the first name in another, and then see which records show
up in both, and check the address on those records? No, that's way
too slow, especially the part about "see which records show up in
both". You look up in one index, then scan for records that match
the criteria. Real phone books are indexed on (last name, first
name) and if such an index is available, that's the best to use.
I don't yet have a
compound index on these fields.

If the existing query is too slow, that's what you should investigate.

Gordon L. Burditt