# Query Tuning for Function cast and AES\_DECRYPT

**URL:** <https://forums.percona.com/t/query-tuning-for-function-cast-and-aes-decrypt/18873>\
**Category:** MySQL & MariaDB\
**Tags:** mysql\
**Created:** [December 1, 2022, 7:04pm UTC](https://forums.percona.com/t/query-tuning-for-function-cast-and-aes-decrypt/18873 "2022-12-01T19:04:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pavanmysql](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pavanmysql/32/7152_2.png) [@Pavanmysql](https://forums.percona.com/u/Pavanmysql)\
**Post date:** [December 1, 2022, 7:04pm UTC](https://forums.percona.com/t/query-tuning-for-function-cast-and-aes-decrypt/18873/1 "2022-12-01T19:04:01Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 2, 2022, 3:33am UTC](https://forums.percona.com/t/query-tuning-for-function-cast-and-aes-decrypt/18873/2 "2022-12-02T03:33:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [December 2, 2022, 6:22am UTC](https://forums.percona.com/t/query-tuning-for-function-cast-and-aes-decrypt/18873/3 "2022-12-02T06:22:28Z")

</div>

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](https://www.percona.com/blog/mysql-8-0-functional-indexes/)