Terkadang kita menggunakan fungsi dalam klausa WHERE query Kita, seperti contoh query berikut :
SELECT * FROM tbl_penjualan WHERE CONCAT(LEFT(cnofaktur,3),RIGHT(cnofaktur,LENGTH(cnofaktur)-4))= CONCAT(LEFT('U10K14100000004',3),RIGHT('U10K14100000004',LENGTH('U10K14100000004')-4));
Ketika data tbl_penjualan sangat besar, query ini akan berjalan cukup lama karena klausa WHERE tidak menggunakan index.
mysql> SELECT count(*) FROM tbl_penjualan WHERE CONCAT(LEFT(cnofaktur,3),RIGHT(cnofaktur,LENGTH(cnofaktur)-4))= CONCAT(LEFT('U10K14100000004',3),RIGHT('U10K14100000004',LENGTH('U10K14100000004')-4));
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.48 sec)
Bandingkan dengan yang tanpa fungsi :
mysql> SELECT count(*) FROM tbl_penjualan WHERE cnofaktur='U10K14100000004';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
Kalau di PostgreSQL, ada yang dinamakan Functional Indexes atau Indexes on Expressions [1], apakah ada solusi serupa di MySQL ?
Alhamdulillah, solusi nya ada, yaitu menggunakan Virtual Columns, tapi jangan lupa, virtual column ini harus diindex juga agar querynya cepat, fitur ini mulai ada di MySQl 5.7.8 [2][3][4]
Contoh :
ALTER TABLE tbl_penjualan ADD cnofaktur_tanpa_jenisbayar VARCHAR(14) GENERATED ALWAYS AS ( CONCAT(LEFT(cnofaktur,3),RIGHT(cnofaktur,LENGTH(cnofaktur)-4)) ) VIRTUAL AFTER cnofaktur;
ALTER TABLE tbl_penjualan ADD INDEX(cnofaktur_tanpa_jenisbayar);
mysql> SELECT count(*) FROM tbl_penjualan WHERE cnofaktur_tanpa_jenisbayar='U1014100000004';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
Referensi
- Indexes on Expressions, https://www.postgresql.org/docs/9.1/static/indexes-expressional.html
- Virtual Columns and Effective Functional Indexes in InnoDB, http://mysqlserverteam.com/virtual-columns-and-effective-functional-indexes-in-innodb/
- Virtual columns in MySQL and MariaDB, https://www.percona.com/blog/2016/03/04/virtual-columns-in-mysql-and-mariadb/
- Generated Columns, https://dev.mysql.com/doc/search/?d=12&p=1&q=Generated+Columns
