Query Tuning for Function cast and AES_DECRYPT

we have table user with column username varbinary(200) NOT NULL.

Query:
SELECT id, password, source_name, original_code
from users
WHERE
CAST(AES_DECRYPT(username, “Pass2020”) as char) = BINARY’600002’
AND roleid = 1 AND is_deleted != 1 AND isclosed !=1

The index on column “username” not working. how to optimize such query?

You are using a function on the column. The index is created with the values in the table, not values generated by the function. Mysql must decrypt every row in order to do the comparison.

2 Likes

In case you’re using MySQL 8, you better be aware of functional indexes which may rescue such queries: MySQL 8.0 Functional Indexes - Percona Database Performance Blog

2 Likes