List:General Discussion« Previous MessageNext Message »
From:Randolph Chung Date:April 17 1999 11:30am
Subject:Re: BUG REPORT: Index usage anomalies
View as plain text  
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
retrieval time.

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

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?

thanks again,
 ( >__< )
 ^^ ~~ ^^
BUG REPORT: Index usage anomaliesRandolph Chung17 Apr
  • BUG REPORT: Index usage anomaliesMichael Widenius17 Apr
    • Re: BUG REPORT: Index usage anomaliesRandolph Chung17 Apr
      • Re: BUG REPORT: Index usage anomaliesMichael Widenius18 Apr