Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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()
1 2 3 4 5 6 7 8 9 10 11 | mysql> select * from test2; +-----+----------+ | id | totalnum | +-----+----------+ | 1 | 1 | | 2 | 6 | | 3 | 10 | | 50 | 50 | | 100 | 100 | +-----+----------+ 5 rows in set (0.00 sec) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain select * from test2 where mod(totalnum,4)=2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) |
And when there are huge rows, performance is going to be a big problem.
1 2 | mysql> alter table test2 add index idx_mod5_totalnum(mod(totalnum,5)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod(totalnum,5))' at line 1 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | mysql> alter table test2 add column mod4totalnum int as (mod(totalnum,4)); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from test2; +-----+----------+--------------+ | id | totalnum | mod4totalnum | +-----+----------+--------------+ | 1 | 1 | 1 | | 2 | 6 | 2 | | 3 | 10 | 2 | | 50 | 50 | 2 | | 100 | 100 | 0 | +-----+----------+--------------+ 5 rows in set (0.00 sec) |
And also, we can build an index on the generated column:
1 2 3 | mysql> alter table test2 add index idx_mod4totalnum(mod4totalnum); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Then we can use the function-like index:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain select * from test2 where mod(totalnum,4)=2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test2 partitions: NULL type: ref possible_keys: idx_mod4totalnum <strong>key: idx_mod4totalnum</strong> key_len: 5 ref: const rows: 3 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) |
Or you can just use the generated column name in the WHERE clause:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain select * from test2 where mod4totalnum = 2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test2 partitions: NULL type: ref possible_keys: idx_mod4totalnum <strong>key: idx_mod4totalnum</strong> key_len: 5 ref: const rows: 3 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) |
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.
