>>>>> "Randolph" == Randolph Chung <randolph@stripped> writes:
Randolph> 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.
Randolph> Does "row" here refer to the number of (predicted) result rows? Or are you
Randolph> talking about index pages?
Yes; Row refers to the number of predicated results rows.
Randolph> Where does mysql gather this row info? Is it from a catalog? Because the
Randolph> info seems to not correspond with the data very well. In my case, the date
Randolph> ranges from 19000101 to 19991231, but even if i do a search for dates >
Randolph> 19991215 (or in fact, any date in december) it will not use the index.
Can you mail me an example of this? (with count(*) of the original
query + an EXPLAIN).
MySQL estimates the number of rows from the index tree; It's usually
quite accurate (typical miss rate +/- 10%)
Randolph> Related to this, I can never get mysql to use the index of the queries are
Randolph> of the form:
Randolph> select * from foo where start_date > 19990101 and end_date < 19990131
Randolph> or
Randolph> select * from foo where start_date = 19990101 or start_date = 19990102
Randolph> It seems to me that using an index here will cut down greatly on the
Randolph> retrieval time.
An example would help! MySQL should use indexes for the above queries.
>> 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
>> sequentially.
Randolph> is there a way to get mysql to reorder the data so you get a clustered
Randolph> index? And if so, will the query optimizer take this into account? If I am
Randolph> querying a set of relatively static data, this should save me a lot of
Randolph> time....
Sorry, MySQL don't yet support clustered indexes.
>> If the number of rows matched by the index is higher than the above,
>> then table scanning is used.
Randolph> i guess i should dig in the code for this, but......
Randolph> does mysql use a isam index or a b+-tree index?
MySQL uses a compressed B-tree index (the index is stored in a
separated file from the data)
Regards,
Monty
