Faster Queries Through a Distinct Key Count

It was found that a complex (not prepared) query could run slow with given search values compared with other values. The query consisted of many inner and outer joins on large tables with duplicate indices containing few keys matching the vast majority of the records and other keys matching very few records. To better optimize these queries required an accurate estimation of the duplicate index selectivity, which implies knowing how many distinct keys there are in an index allowing duplicates. This feature has been added for c-tree indices.

To support this feature, an index requires a distinct key count attribute. A utility, ctdbdistinct, is available to add the distinct key count feature to existing indices in a c-treeSQL database.

The c-treeSQL Server query optimizer was then updated to take advantage of this distinct key count. With this feature enabled, all indices are created by default with this attribute available.