Alter Table Add Column If Not Exists

Terkadang kita ingin melakukan proses penambahan kolom pada sekumpulan database yang memiliki struktur tabel yang sama, tapi ada beberapa yang sudah ditambahkan dan ada yang belum, tapi jika melihatnya satu persatu akan memakan waktu. 

Jadi, bagaimana cara menambahkan kolom baru pada suatu tabel, hanya jika kolom tersebut belum ada ?

Begini caranya :

SET @s = (SELECT IF(
   (SELECT COUNT(*)
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_name = 'nama_tabel'
       AND table_schema = DATABASE()
       AND column_name = 'nama_kolom'
   ) > 0,
   'SELECT 1',
   'ALTER TABLE nama_tabel ADD nama_kolom INT(9) NULL DEFAULT 0 AFTER nama_kolom_yg_sudah_ada;'
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MySQL Functional Indexes

Status : Draft 

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

1. Indexes on Expressions, https://www.postgresql.org/docs/9.1/static/indexes-expressional.html
2. Virtual Columns and Effective Functional Indexes in InnoDB, http://mysqlserverteam.com/virtual-columns-and-effective-functional-indexes-in-innodb/
3. Virtual columns in MySQL and MariaDB, https://www.percona.com/blog/2016/03/04/virtual-columns-in-mysql-and-mariadb/
4. Generated Columns, https://dev.mysql.com/doc/search/?d=12&p=1&q=Generated+Columns

Tuning : MySQL LEFT JOIN

Tips : Hindrari Subquery pada left join.

Contoh yang buruk :

SELECT Count(*)
FROM   pengawas p
       LEFT JOIN (SELECT *
                  FROM   pengawas_periode
                  WHERE  periode_id = '2') AS pp
              ON pp.pengawas_id = p.id
       LEFT JOIN lencana_pengawas lp
              ON p.ingin_lencana_pengawas = lp.id
       LEFT JOIN panlok
              ON p.panlok_id = panlok.id
WHERE  1 = 1
       AND p.fc_no_identitas IS NOT NULL
       AND p.foto IS NOT NULL
       AND pp.pengawas_id IS NULL
       AND ( ( Lower(p.nama) LIKE '%a%'
                OR Lower(lp.singkatan) = 'a' )
              OR p.no_identitas = 'a' )
       AND p.is_aktif = 1
       AND ( p.instansi = '77'
              OR p.instansi IN( 'Instansi A', 'Instansi B' ) )
       AND ( p.ingin_lencana_pengawas = '22'
              OR p.ingin_lencana_pengawas = '3' )
        OR p.id = 'a'
ORDER  BY p.angkatan ASC,
          p.created_at ASC; 

Contoh yang baik, query ini jauh lebih cepat dari query diatas  :

SELECT Count(*)
FROM   pengawas p
       LEFT JOIN pengawas_periode pp
              ON p.id = pp.pengawas_id
                 AND pp.periode_id = 2
       LEFT JOIN lencana_pengawas lp
              ON p.ingin_lencana_pengawas = lp.id
       LEFT JOIN panlok
              ON p.panlok_id = panlok.id
WHERE  1 = 1
       AND pp.pengawas_id IS NULL
       AND p.fc_no_identitas IS NOT NULL
       AND p.foto IS NOT NULL
       AND ( ( Lower(p.nama) LIKE '%a%'
                OR Lower(lp.singkatan) = 'a' )
              OR p.no_identitas = 'a' )
       AND p.is_aktif = 1
       AND ( p.instansi = '77'
              OR p.instansi IN( 'Instansi A', 'Instansi B' ) )
       AND ( p.ingin_lencana_pengawas = '22'
              OR p.ingin_lencana_pengawas = '3' )
        OR p.id = 'a'
ORDER  BY p.angkatan ASC,
          p.created_at ASC