Optimizing MySQL queries where clause contains function by using Generated Columns

Description:

As we know, we can’t create a function-based index on MySQL, not like PostgreSQL (https://www.postgresql.org/docs/current/indexes-expressional.html). Usually, we should avoid using functions in the where clause, but what can we do when facing this problem?

Let’s have a try:

MySQL version: 8.0.12

Function name: modulo, mod()

And when there are huge rows, performance is going to be a big problem.

Don’t work, that’s fine~

What can we do?

From MySQ-5.7.5, we can create a generated column to store the result of an expression(http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/). That means we can add a column to store the mod() values:

And also, we can build an index on the generated column:

Then we can use the function-like index:

Or you can just use the generated column name in the WHERE clause:

That’s how to optimize modulo value at a MySQL database. Also you can store some other functions’ result at the column and build an index on it.

Shuo Wang
Author: Shuo Wang

Leave a Reply

Your email address will not be published. Required fields are marked *