>>>>> "Dan" == Dan Nelson <dnelson@stripped> writes:
Dan> In the last episode (Oct 17), Bob Kline said:
>> On Sun, 17 Oct 1999, Mike Schwartz wrote:
>> > I am suggesting a modification to MySQL that would be beneficial is
>> > what's called an index scan or full index scan. Rather than using
>> > the index for hashed or b-tree fast lookups of single key or index
>> > values, scan the whole thing.
>> That's why I asked how large the keys were for the column being
>> indexed. Obviously the smaller the ratio between the values in this
>> column and the size of the complete rows from which these values are
>> drawn, the more profitable such a scheme is likely to be.
Dan> Something that might keep MySQL from being able to take advantage of
Dan> this (potentially very fast) search method is the fact that for any
Dan> particular table, all indexes are contained in a single file.
Dan> Depending on how MySQL stores indexes, it might have to walk this
Dan> entire indexfile (or do random I/O within it, even worse). I have a
Dan> couple of tables where my .MYI file is larger than my .MYD file, due to
Dan> the number of indexes.
Dan> Oracle stores indexes separately from each other, and I know that a
Dan> full-index scan is much faster than a full-table scan there.
On the other hand, Oracle is much slower on full table scan (or the
main index scan) than MySQL as in this case Oracle has to retrieve the
data rows in random order.
Anyway; The 'easy' (?) way to get the index scanning faster is to do
'isamchk -Si *.ISM'.
Currently you must of course take down MySQL while you run the
above (but this is usually very fast to just sort the indexes)...