G
George Potemkin
Guest
The article that Mike Fechner pointed out says: The compiler uses the following rules for ranking the available indexes when analyzing brackets and deciding which indexes to make use of: 1) An index that was specified in USE-INDEX 2) A unique index when all key components are used for equality matches. 3) The index with the most equality matches on leading index fields 4) The index with the most range matches 5) Word indexes referenced by the contains operator 6) The index with the most sort matches (BY) 7) A table's primary index 8) The index that comes first alphabetically, by index name If a primary index is unique then 2) will work and 7) will not (except when the WHERE clause is empty). In other words a compiler will not choose an index based on the "primary" attribute - like if the "primary" attribute does not exist at all. Also the unique indexes used to be the bad candidates to read the bunch of the records. Probably it would be better if the compiler will avoid to choose the unique indexes without equality matches - even when it's a primary index.
Continue reading...
Continue reading...