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?
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.
Why not just
WHERE D > 'somestring'
This would leverage a typical B-tree index on column D.
If you perform comparisons only by passing column values to a function such as STRCMP(), there is no value in indexing it.
Reference: https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.
So, you may use
SELECT A, B, C FROM TABLE WHERE D > 'somestring'
instead, while LIKE is more strict and could be what you expect.
Also, you can use the COLLATE operator to convert the column to a case-sensitive collation, to make sure it's comparing the case as well.
SELECT A, B, C FROM TABLE WHERE D > 'somestring' COLLATE utf8_bin
LIKE without a wildcard character is the same as =. I think the OP's intent was as a sorting mechanism of sorts - return all records where D is greater than (in alphanumeric sorting terms) the string against which the table records are being compared.
Mysql will not use indexes for function calls.
Use below query And Create index on D column
SELECT A, B, C FROM TABLE WHERE D ='somestring'
to create index use the below query
create index index1 on TABLE(D);