0

My JAVA code is using below select statement for fetching data. Col_1 is an index column but looking into EXPLAIN, i am seeing that this select statement is not utilizing the index. If i remove UPPER function, it is utilizing index but I don't want to change my java code.

SELECT *FROM MY_TABLE WHERE UPPER(COL_1) = UPPER(input_value);

In ORACLE we can create functional index by using below statement but below index creation statement is not working in MySQL (8.0.12-commercial , innodb_version 8.0.12)

CREATE INDEX MY_INDEX ON MY_TABLE (UPPER(COL_1));
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
RU Ahmed
  • 558
  • 4
  • 23
  • Does this answer your question? [Can we create functional indexes in MySql for UPPERCASE and LOWERCASE functions](https://stackoverflow.com/questions/30006349/can-we-create-functional-indexes-in-mysql-for-uppercase-and-lowercase-functions) – Sebastian Brosch Apr 01 '20 at 06:55
  • 1
    Are you sure that you're using a case sensitive collation? – Strawberry Apr 01 '20 at 07:24

2 Answers2

3

MySQL 8.0.13 allows indexed expressions.

create index upper_col1 on my_table ( (upper(col_1)) )

You can also solve this by ensuring all your values are normalized and keep them normalized with a trigger.

update my_table col_1 = upper(col_1);

create trigger normalize_col_1_on_insert
  before insert on my_table
  for each row
begin
  NEW.col_1 = upper(NEW.col_1)
end

create trigger normalize_col_1_on_update
  before update on my_table
  for each row
begin
  NEW.col_1 = upper(NEW.col_1)
end

Then be sure to also normalize your value.

SELECT *
FROM MY_TABLE
WHERE COL_1 = UPPER(input_value);
Schwern
  • 153,029
  • 25
  • 195
  • 336
-1

I don't think there is a valid reason for doing

UPPER(COL_1) = UPPER(input_value)

instead of doing

COL_1 = input_value

together with a case-folding COLLATION on COL_1. That would let = take care of doing the 'right' thing. There are probably cases where UPPPER() does the 'wrong' thing. (I am thinking of things like the German ß.)

Functional index

This also works:

ALTER TABLE MY_TABLE
    ADD INDEX ucol_1 ((UPPER(COL_1)));

Be sure to use those extra parentheses.

(I verified it on a small test in Percona 8.0.16.)

Rick James
  • 135,179
  • 13
  • 127
  • 222