Thanks for the quick reply.
> As you can see, in the last entry the number of rows is much
> larger. In this case it makes sense to use ALL instead of range.
Does "row" here refer to the number of (predicted) result rows? Or are you
talking about index pages?
Where does mysql gather this row info? Is it from a catalog? Because the
info seems to not correspond with the data very well. In my case, the date
ranges from 19000101 to 19991231, but even if i do a search for dates >
19991215 (or in fact, any date in december) it will not use the index.
Related to this, I can never get mysql to use the index of the queries are
of the form:
select * from foo where start_date > 19990101 and end_date < 19990131
select * from foo where start_date = 19990101 or start_date = 19990102
It seems to me that using an index here will cut down greatly on the
> In the later case it's better that MySQL doesn't use indexes as the
> query will be resolved faster in this case! The problem with using
> indexes is that the rows are retrieved in a random order instead of
is there a way to get mysql to reorder the data so you get a clustered
index? And if so, will the query optimizer take this into account? If I am
querying a set of relatively static data, this should save me a lot of
> If the number of rows matched by the index is higher than the above,
> then table scanning is used.
i guess i should dig in the code for this, but......
does mysql use a isam index or a b+-tree index?
( >__< )
^^ ~~ ^^