From: Michael Widenius
Date: April 17 1999 8:14pm
Subject: Re: BUG REPORT: Index usage anomalies
List-Archive: http://lists.mysql.com/mysql/1993
Message-Id: <14104.60101.489191.519242@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "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