dolt icon indicating copy to clipboard operation
dolt copied to clipboard

Support Functional Key Parts (aka expressions in indexes)

Open nicktobey opened this issue 6 months ago • 0 comments

MySQL allows indexes to be defined using expressions, as described here: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

The main benefit of this is that is allows the engine to use these indexes when the same expression appears in a query. Common uses might include:

  • The length of a string column
  • A substring of a string column
  • A lookup into a JSON object

Dolt does not currently support functional key parts. The same effect can be produced using an index on a generated column, with some minor drawbacks:

  • The expression value gets stored in both the primary index and the secondary index, instead of just the secondary index.
  • The schema has an extra field that isn't intended for use.

nicktobey avatar Aug 14 '24 17:08 nicktobey