Edwin Lee Edwin Lee - 4 months ago 21
SQL Question

MySQL - Indexing for String Comparison

This is for InnoDB with MySQL 5.7.

If I have a query like:

SELECT A, B, C FROM TABLE WHERE STRCMP(D, 'somestring') > 0


Is it possible to have an index on
D
which can be used by the query? i.e. is MySQL smart enough to use the btree index for STRCMP function?

If not, how might I be able to redesign the query (and/or table) such that I can do string comparison on D, and there can be some form of pruning so that it does not have to hit every single row?

Answer

Amazing how many wrong answers so far. Let me see if I can not join them.

STRCMP returns -1, 0, or 1, depending on how the arguments compare.

STRCMP(D, 'somestring') > 0 is identical to D > 'somestring'. (Not >=, not =, not LIKE)

Actually, there may be collation differences, but that can be handled if necessary.

Any function, and certain operators, 'hide' columns from use with INDEXes. D > 'somestring' can benefit from an index starting with D; the STRCMP version cannot.