Simple SQL Query Performance Tuning using INDEX

tuning1.sql

E:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test ;
Database changed

mysql> -- --------------------------------------------------
mysql> -- coba bikin sebuah table raksasa dengan 9 juta rows
mysql> -- setelah selesai dibuat, table ini belum memiliki index
mysql> -- --------------------------------------------------
mysql> create table big_table as
    -> select @baris := @baris+1 as baris
    -> from ( select @baris := 0 ) x
    -> join ( select 1 kolom from information_schema.tables ) a
    -> join ( select 1 kolom from information_schema.tables ) b
    -> join ( select 1 kolom from information_schema.tables ) c ;
Query OK, 9938375 rows affected (2 min 25.70 sec)
Records: 9938375  Duplicates: 0  Warnings: 0

mysql> -- --------------------------------------------------
mysql> -- coba hitung jumlah row yang ada di dalam big_table
mysql> -- terlihat mysql memerlukan waktu 19 detik 
mysql> -- untuk mendapatkan hasil yang kita inginkan
mysql> -- --------------------------------------------------
mysql> select count(*) from big_table ;
+----------+
| count(*) |
+----------+
|  9938375 |
+----------+
1 row in set (18.82 sec)

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql melakukan full table scan 
mysql> -- ditandai dengan type=ALL ke seluruh 9 juta rows
mysql> -- --------------------------------------------------
mysql> explain
    -> select count(*) from big_table ;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | big_table | ALL  | NULL          | NULL | NULL    | NULL | 9938689 |       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

mysql> -- --------------------------------------------------
mysql> -- coba kita test dengan query yang lain
mysql> -- yaitu mendapatkan sebuah row yang kira2 
mysql> -- berada di tengah2 table besar ini
mysql> -- lagi2 terlihat mysql memerlukan waktu 19 detik 
mysql> -- untuk mendapatkan hasil yang kita inginkan
mysql> -- --------------------------------------------------
mysql> select * from big_table
    -> where baris = 5346781 ;
+---------+
| baris   |
+---------+
| 5346781 |
+---------+
1 row in set (19.03 sec)

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat juga bahwa mysql melakukan full table scan 
mysql> -- ditandai dengan type=ALL ke seluruh 9 juta rows
mysql> -- *padahal* kita hanya perlu 1 row saja yang ada di tengah
mysql> -- --------------------------------------------------
mysql> explain
    -> select * from big_table
    -> where baris = 5346781 ;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | big_table | ALL  | NULL          | NULL | NULL    | NULL | 9938977 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> -- --------------------------------------------------
mysql> -- sekarang coba kita buat INDEX berdasarkan field baris
mysql> -- harap bersabar... karena cukup lama juga :)
mysql> -- --------------------------------------------------
mysql> create index big_table_pk on big_table(baris) ;
Query OK, 0 rows affected (2 min 4.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- --------------------------------------------------
mysql> -- sekarang kita hitung  row yang ada di dalam big_table
mysql> -- rupanya terlihat ada peningkatan performance 
mysql> -- dari awalya perlu 19 detik, menjadi 10 detik saja
mysql> -- LUAR BIASA!
mysql> -- --------------------------------------------------
mysql> select count(*) from big_table ;
+----------+
| count(*) |
+----------+
|  9938375 |
+----------+
1 row in set (10.91 sec)

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql tidak lagi melakukan full table scan 
mysql> -- namun melakukan index scan, jauh lebih cepat dari full table scan
mysql> -- --------------------------------------------------
mysql> explain
    -> select count(*) from big_table ;
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | big_table | index | NULL          | big_table_pk | 9       | NULL | 9938977 | Using index |
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> -- --------------------------------------------------
mysql> -- sekarang kita test dengan query kedua
mysql> -- dan hasilnya sangat LUAR BIASA!
mysql> -- hanya perlu 0.05 detik dari sebelumnya 19 detik !!!
mysql> -- --------------------------------------------------
mysql> select * from big_table
    -> where baris = 5346781 ;
+---------+
| baris   |
+---------+
| 5346781 |
+---------+
1 row in set (0.05 sec)

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql melakukan index "big_table_pk" 
mysql> -- dan langsung menuju rows yang kita inginkan
mysql> -- --------------------------------------------------
mysql> explain
    -> select * from big_table
    -> where baris = 5346781 ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | big_table | ref  | big_table_pk  | big_table_pk | 9       | const |    1 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> -- --------------------------------------------------
mysql> -- SEMOGA BERGUNA!
mysql> -- --------------------------------------------------
mysql>