Querying a Single Table

Info

Database, table dan records yang digunakan pada sesi ini anda dapat merujuk ke resource. Dan untuk menjamin kelancaran penggunaan setiap query yang ada pada catatan ini, maka saya sarankan untuk menggunakan schema lahmansbaseballdb, jika belum ada buat schema dengan nama tersebut.

Warning

Jika query dibawah ini tidak mensiratkan penggunaan nama schema maka saya menggunakan USE lahmansbaseballdb; pada session saya. Anda dapat mengikuti cara tersebut atau selalu siratkan nama scema anda jika menggunakan nama selain lahmansbaseballdb.

Contoh menyiratkan nama schema

    SELECT * 
    FROM nama_data_base.contoh_table;

SELECT statement and FROM statement

Select semua kolom

    SELECT * FROM appereances;

Membatasi untuk mengambil beberapa kolom

    SELECT teamID, lgID FROM appearances;

Columns aliases

Column aliases membuat kita dapat menggunakan nama kolom yang berbeda pada query, gunakan keyword AS.

SELECT playerID as player_gua FROM appearances;

Output

+------------+
| player_gua |
+------------+
| barnero01  |
| barrofr01  |
| birdsda01  |
| conefr01   |
| gouldch01  |
| .........  |

Atau dapat menggunakan

SELECT playerID as 'player kita' FROM appearances;

Output

+------------+
| player kita|
+------------+
| barnero01  |
| barrofr01  |
| birdsda01  |
| conefr01   |
| gouldch01  |
| .........  |

Kita dapat menggunakan aliases pada ORDER BY, GROUP BY dan HAVING akan tetapi tidak dapat digunakan pada klausa WHERE.

Using Use Statment

Statemen ini digunakan untuk merujuk pada database tertentu, sehingga penulisan sebuah query menjadi lebih singkat. Jika tidak menggunakan statement ini misal maka harus ditulis namadatabase.namatable

• Tidak menggunakan USE

  SELECT * FROM lahmansbaseballdb.appearances;
  

• Menggunakan USE

  SELECT * FROM appearances;
  

Using DISTINCT clause

Klause ini jika digunakan pada statement SELECT, akan mengembalikan hanya nilai yang berbeda.

SELECT DISTINCT playerID from appearances;

Output

+-----------+
| playerID  |
+-----------+
| barnero01 |
| barrofr01 |
| birdsda01 |
| conefr01  |
| gouldch01 |
| ......... |

Using LIMIT Clause

Klause ini jika digunakan pada statement SELECT hanya akan mengembalikan sebanyak nilai yang ditentukan pada LIMIT.

SELECT playerID from appearances LIMIT 10;

Query diatas hanya mengambil 10 baris peratama pada sebuah tabel.

Jika ingin mengambil \(n\) baris dimulai pada baris \(k\) maka dapat menggunakan OFFSET clause

SELECT playerID FROM appearances LIMIT 10 OFFSET 3;

Query diatas mengambil 10 baris dimulai dari baris ke-3.

Using WHERE, AND & OR Clause

Klause ini digunakan untuk membatasi keluaran yang dihasilkan berdasarkan kondisi tertentu, misalkan;

SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE g_all > 40; 

Query diatas akan menampilkan pemain dengan g_all (akumulasi pemain bermain) yang lebih dari 40.

Anda juga dapat menggunakan lebih dari WHERE menggunakan AND and OR. Serta dapat menggunakan AND and OR sekaligus,

SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE (g_all > 40 AND g_defense < 30)
OR g_all > 60; 

Using NOT, IN dan BETWEEN

klause IN membuat anda dapat menggunakan daftar tertentu yang digunakan sebagai nilai atau kondisi tertentu yang berdampak pada nilai pada sebuah query. Misalkan anda ingin mengeluarkan nilai pada tabel appearances dengan ketentuan akumulasi permainan dengan jumlah 40,50 dan 60;

SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE g_all IN (40, 50, 60); 

BETWEEN berbeda dengan IN, klause ini mengembalian sebuah ketentuan yang berada diantara kedua nilai

SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE g_all BETWEEN 40 and 60; 

Sedangkan klause NOT akan menampilkan sebuah nilai yang berada diluar dari ketentuan yang ditetapkan

SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE g_all NOT BETWEEN 40 and 60; 

Dan anda dapat menggabungkan NOT, INT, BETWEEN serta lAND

SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE g_all NOT IN (40, 50, 60)
AND g_batting NOT BETWEEN 30 and 40; 

Using LIKE operators and wildcards

Penggunaan perator LIKE dan wildcards sangat bergunaa ketika anda ingin mencari sebuah nilai pada tabel yang tidak membutuhkan keseluruhan karakter. Misalkan anda ingin mencari nama pemain yang mengandung kata “hode”. Dibawah ini adalah tipe dari wildcards yang dapat digunakan didlam MySql

Wildcard characters	Character description	How it works
%	Percent sign	Representasi kosong hingga lebih dari satu karakter
_	Underscore	Representasi dari satu karakter

Using % widlcard

% wildcard merepresentasi 0 karakter atau lebih. Misalkan anda disuruh untuk mencari player ID yang diawail dengan huruf a.

SELECT playerID, G_all, G_batting, G_defense 
FROM lahmansbaseballdb.appearances 
WHERE playerID LIKE "a%";

Query diatas artinya : “cari pada kolom playerID nama yang diawal dengan huruf a dan diikuti oleh karakter apapun dari 0~tak hingga pada tabel appearances”

Output

+-----------+-------+-----------+-----------+
| playerID  | G_all | G_batting | G_defense |
+-----------+-------+-----------+-----------+
| allisar01 |    29 |        29 |        29 |
| armstbo01 |    12 |        12 |        12 |
| addybo01  |    25 |        25 |        25 |
| ansonca01 |    25 |        25 |        25 |
| ......................................... |

Contoh diatas kita meletakan % setelah karakter a, namun bagai mana jika tempatkan diawal ?

SELECT playerID, G_all, G_batting, G_defense 
FROM lahmansbaseballdb.appearances 
WHERE playerID LIKE "%a";

Query diatas artinya : “cari pada kolom playerID nama yang diawal dengan karakter apapun dari 0~tak hingga dan diikuti oleh huruf a pada tabel appearances”

Oke, query diatas tidak menghasilkan 0 baris, yup karena semua playerID selalu diakhiri dengan angka.

Sehingga, jika kita menempatkan karakter % pada awal dan akhir dari sebuah kata atau kalimat, maka dapat kita artikan, cari karakter yang mengandung kata tersebut. Misalkan

SELECT playerID, G_all, G_batting, G_defense 
FROM lahmansbaseballdb.appearances 
WHERE playerID LIKE "%a%";

Output

+-----------+-------+-----------+-----------+
| playerID  | G_all | G_batting | G_defense |
+-----------+-------+-----------+-----------+
| barnero01 |    31 |        31 |        31 |
| barrofr01 |    18 |        18 |        18 |
| birdsda01 |    29 |        29 |        29 |
| jackssa01 |    16 |        16 |        16 |
| mcveyca01 |    29 |        29 |        29 |
| schafha01 |    31 |        31 |        31 |
| ......................................... |

Warning

hindari penggunakan % pada awal karakter karena akan memperlambat proses query.

Using _ wildcard

Berbeda degan %, _ hanya mencari satu karakter saja. Misalkan “a_a”, cari karakter yang diawal dengan huruf a diikut dengan satu buah karakter apapun dan diakhiri dengan huruf a. Sebagai contoh, buatlah sebuah query untuk mencari round pada tabel fieldingpost yang mana karakter kedua dan keempat disi dengan huruf L & S.

SELECT *
FROM lahmansbaseballdb.fieldingpost 
WHERE round LIKE "_L_S_";

Output

+-----------+--------+--------+------+-------+-----+------+------+---------+------+------+------+------+------+------+------+------+
| playerID  | yearID | teamID | lgID | round | POS | G    | GS   | InnOuts | PO   | A    | E    | DP   | TP   | PB   | SB   | CS   |
+-----------+--------+--------+------+-------+-----+------+------+---------+------+------+------+------+------+------+------+------+
| abbotku01 |   2000 | NYN    | NL   | NLDS2 | SS  |    1 |    0 |      12 |    0 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
| abbotpa01 |   2000 | SEA    | AL   | ALDS2 | P   |    1 |    1 |      17 |    0 |    1 |    0 |    0 |    0 | NULL | NULL | NULL |
| abbotpa01 |   2001 | SEA    | AL   | ALDS1 | P   |    1 |    0 |       9 |    0 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
| abreubo01 |   2006 | NYA    | AL   | ALDS1 | RF  |    4 |    4 |     102 |    4 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
| abreubo01 |   2007 | NYA    | AL   | ALDS2 | RF  |    4 |    4 |     110 |    7 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
| abreubo01 |   2009 | LAA    | AL   | ALDS2 | RF  |    3 |    3 |      81 |    8 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
| aceveal01 |   2009 | NYA    | AL   | ALDS1 | P   |    1 |    0 |       3 |    0 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
| ................................................................................................................................ |

Escaping wildcard values

JIka kalimat yang anda cari memliki karakter wildcard didalamnya. Maka anda harus meng-escape wildcard tersebut agar query bekerja semestinya. Dibawah ini adalah escape karakter beserta karakter-nya

SELECT *
FROM lahmansbaseballdb.fieldingpost 
WHERE playerID LIKE '%\"%';

Atau anda dapat ingin menggunakan karater lain selain backslash, anda dapat mengaturnya menggunakan operator ESCAPE

SELECT *
FROM lahmansbaseballdb.fieldingpost 
WHERE playerID LIKE '%^"%' ESCAPE "^"; --- (1)!

1. Operator ESCAPE menggatin \ menjadi ^.

Output

+----------+--------+--------+------+-------+-----+------+------+---------+------+------+------+------+------+------+------+------+
| playerID | yearID | teamID | lgID | round | POS | G    | GS   | InnOuts | PO   | A    | E    | DP   | TP   | PB   | SB   | CS   |
+----------+--------+--------+------+-------+-----+------+------+---------+------+------+------+------+------+------+------+------+
| farr"as  |   2000 | CHA    | AL   | ALDS2 | CF  |    1 |    0 |       9 |    1 |    0 |    0 |    0 |    0 | NULL | NULL | NULL |
+----------+--------+--------+------+-------+-----+------+------+---------+------+------+------+------+------+------+------+------+
1 row in set (0.02 sec)

How to filter NULL value

Anda tidak dapat memfilter nilai NULL menggunakan operator seperti =, >, < dan <>. Anda harus menggunakan operatnor IS NULL atau IS NOT NULL

SELECT playerID, G_all, G_batting, G_defense 
FROM lahmansbaseballdb.appearances 
WHERE G_defense IS NULL;

Output

+-----------+-------+-----------+-----------+
| playerID  | G_all | G_batting | G_defense |
+-----------+-------+-----------+-----------+
| barrebi01 |     1 |         1 |      NULL |
| brainas01 |    16 |        16 |      NULL |
| careyto01 |    56 |        56 |      NULL |
| cravebi01 |    41 |        41 |      NULL |
| cummica01 |    42 |        42 |      NULL |
| forceda01 |    49 |        49 |      NULL |
| hallge01  |    35 |        35 |      NULL |
| ......................................... |  

Using ORDER BY clause

Klausa ORDER BY ditempatkan setelah FROM dan setelah WHERE jika menggunakan klausa tersebut. Denga klausa ini kita dapat mengurutkan kolom secara ascending atau descending. Ascending adalah pengurutan bawaan yang ditetapkan oleh MySql.

Untuk mengurutkan kolom dengan urutan ascending gukana kata kunci ASC dan desceding menggunakan kata kunci DESC.

SELECT playerID, G_all, g_batting, g_defense 
FROM appearances 
WHERE G_all >= 100 
ORDER BY G_all DESC;

Query diatas artinya : “Tampilkan kolom-kolom tersebut dengan kondisi G_all lebih besar atau sama dengan 100 dan urutkan dari yang terbesar ke yang terkecil”

Output

+-----------+-------+-----------+-----------+
| playerID  | G_all | g_batting | g_defense |
+-----------+-------+-----------+-----------+
| willsma01 |   165 |       165 |       165 |
| paganjo01 |   164 |       164 |       164 |
| santoro01 |   164 |       164 |       164 |
| willibi01 |   164 |       164 |       164 |
| tovarce01 |   164 |       164 |       163 |
| robinbr01 |   163 |       163 |       163 |  

Anda juga dapat mengurutkan lebih dari satu buah kolom serta dapat mengurutkan kolom yang berbeda dengan metode urutan yang berbeda.

SELECT playerID, G_all, g_batting, g_defense 
FROM appearances 
WHERE G_all >= 100 
ORDER BY G_all DESC, playerID ASC;

Output

+-----------+-------+-----------+-----------+
| playerID  | G_all | g_batting | g_defense |
+-----------+-------+-----------+-----------+
| willsma01 |   165 |       165 |       165 |
| paganjo01 |   164 |       164 |       164 |
| santoro01 |   164 |       164 |       164 |
| tovarce01 |   164 |       164 |       163 |
| willibi01 |   164 |       164 |       164 |
| bankser01 |   163 |       163 |       162 | 

Warning

Jika dibutuhkan, anda dapat mengurutkan semua kolom pada tabel, akan tetapi akan berdampak pada performa query. Anda harus memperhatikan pemilihan dan berapa banyak kolom yang akan diurutkan.

Using Indexes With Your Queries

Sebagaimana pada bagian Creating and Desinging Database, locking, blocking dan deadlocking memainkan peranan penting pada query. Jika anda menggunakan index secara benar pada query anda, kemungkinan terjadinya locking, blocking dan deadlocking semakin kecil dan hasil query akan semakin lebih cepat.

How to set indexes to query

Untuk melihat bagaimana MySql menjalankan query anda, gunakan klausa EXPLAIN didepan query anda, misalkan;

EXPLAIN SELECT playerID, g_all, g_batting, g_defense
FROM appearances;

Output

+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | appearances | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 105627 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Oke mari kita jelaskan apa maksud dari kolom dan record diatas.

id

Ini adalah nomor urut dari query. Karena query yang kita gunakan sangat sederhana maka hanya muncul satu baris

select type

Kolom ini menceritakan jenis query apa yang dijalankan. Pada kasus ini hasilnya adalah SIMPLE karena hanya berusuan dengan satu buah table.

table

Kolom ini merujuuk pada tabel apa yang berusuan dengan query

partition

Nilai pada contoh ini adalah NULL untuk nonpartitioned table.

type

Kolom ini merujuk pada type dari JOIN table.

possible keys

Kolom ini merujuk pada kemungkinan index yang digunakan oleh query. Karena hasilnya NULL maka tidak ada index yang berhubungan dengan query ini.

key

Kolom ini merujuk pada index apa yang dipiih. NULL pada contoh ini karena tidak ada index yang digunakan pada query ini.

key len

Merujuk pada pangjang dari key yang digunakna pada query ini.

ref

This shows what columns or constraints were compared to the key column. Since the key is NULL, this is also NULL.

rows

Kolom ini menampilkan estimasi jumlah baris yang dihasilkan oleh query.

filtered

Kolom ini menampilkan prosesntasi baris yang tidak terfilter, berdasarkan penggunaan WHERE clasue. Karena pada contoh ini kita tidak menggunakan WHnuytfuERE, maka menghasilkan 100. Jika menggunakan WHERE makan akang ditampilkan nilai kurang dari 100.

extra

This gives you additional information about the query, such as if a WHERE clause was used, if the query used filesort, or if it used temporary tables. Those last two are important for improving performance. If you see using filesort, this means that MySQL had to do an extra pass to retrieve rows in sorted order. If you see using temporary, this means MySQL had to use a temporary table to store values

Oke mari kita coba query dengan tambahan klausa WHERE dan ORDER BY untuk melihat informasi lain pada EXPLAIN.

EXPLAIN SELECT DISTINCT playerID, g_all, g_batting, g_defense
FROM appearances
WHERE playerID LIKE 'a%'
ORDER BY playerID;

Output

+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra|
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | appearances | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 105627 |    11.11 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec) 

Hmm ada beberapa perbedaan dari hasil sebelumnya, 1. pertama, pada kolom filtered, kita hanya mendapatkan perkiraan 11.11% record dari total jumlah baris. 2. Kedua pada kolom Extra, informasinya kita menggunakan klausa WHERE sehingga query membutuhkan "temporary table", dan MySql menggunakan extra pass untuk mengurutkan record.

Note

Pada kasus kasus spesifik ini, karena tabel appearances kecil, dan perkembangan datanya tidak cepat, maka tidak perlu ada perubahan yang dilakukan, namun, jika dirasa perkembanganan besarnya tabel tersebut akan sangat besar dan cepat maka anda harus melakukan perhitungan mengatasi lambatnya query nantinya.

Seandainya tabel tersebut menjadi atau berkembang sangat besar dan cepat, maka apa yang harus dilakukan ? Oke, mari kita coba dan test menggunakan index. Coba jalankan query berikut

EXPLAIN SELECT DISTINCT playerID ---(1)!
      FROM appearances
      WHERE playerID LIKE 'a%'
      ORDER BY playerID;

1. Hanya mengambil kolom playerId dibandingkan dengan query sebelumnya yang mengambil kolom g_all, g_batting dan g_defense.

Output

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra
|
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------------------------------------+
|  1 | SIMPLE      | appearances | NULL       | index | PRIMARY       | PRIMARY | 54      | NULL | 105627 |    11.11 | Using where; Using index; Using temporary |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Karena kita membuang kolom g_all, g_batting dan g_defense dari klausa SELECT, anda dapat melihat bahwa sekarang query menggunakan PRIMARY key (yang mana termasuk tipe clustered index pada tabel ini), anda bisa melihat pada kolom type dan possible_keys diatas. Dengan demikian query ini akan berjalan lebih cepat dari sebelumnya. Akan tetapi, query tersebut tidak menampilkan kolom-kolom yang mungkin kita butuhkan. Jika kita membutuhkan kolom tersebut dan tetap ingin performa yang tinggi maka anda harus menambahkan index baru, non-clustered index. Hal ini disebut dengan adding an index to cover query.

Warning

Namun harus diingat ketika menambahkan index baru, penambahan tersebut akan mempengaruho query lain, mungkin menurunkan tingkat efesiensi dengan menurunya performa inserts, updates dan deletes.

Anda dapat menambahkan index menggunakan MySql Workbrench atau langsung pada session sql pada CMD, untuk catatan ini saya menggunakan Command Line.

Query menabah Non-Clustered

ALTER TABLE `appearances`
ADD INDEX `NC_playerid_g_cols` (`playerID` ASC, `G_all` ASC, `G_batting`
ASC, `G_defense` ASC) VISIBLE;

Query diatas digunakan untuk menambah index, non-clustered index pada table appearances. Sebelum kita jalankan query diatas, mari kita tampilkan informasi mengenai tabel tersebut sehingga kita bisa membandingkan efek dari sebelum dan sesudah menambahkan non-clustered index, menggunakan script dibawah ini.

SHOW INDEX FROM appearances;

Sebelum penamabahan Index

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| appearances |          0 | PRIMARY  |            1 | yearID      | A         |         151 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| appearances |          0 | PRIMARY  |            2 | teamID      | A         |        2904 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| appearances |          0 | PRIMARY  |            3 | playerID    | A         |      105627 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Sesudah penamabahan Index

+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| appearances |          0 | PRIMARY            |            1 | yearID      | A         |         151 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| appearances |          0 | PRIMARY            |            2 | teamID      | A         |        2904 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| appearances |          0 | PRIMARY            |            3 | playerID    | A         |      105627 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| appearances |          1 | NC_playerid_g_cols |            1 | playerID    | A         |       19347 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| appearances |          1 | NC_playerid_g_cols |            2 | G_all       | A         |       97676 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| appearances |          1 | NC_playerid_g_cols |            3 | G_batting   | A         |       99810 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| appearances |          1 | NC_playerid_g_cols |            4 | G_defense   | A         |      102552 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+                    7 rows in set (0.01 sec)

Kita bisa melihat bahwa, penambahan index berhasil dilakukan dengan munculnya key_name dengan nama NC_playerid_g_cols. Sekarang mari kita jalankan script sebelumnya yang menampilkan kolom playerID, g_all, g_bating dan g_defense.

EXPLAIN SELECT DISTINCT playerID, g_all, g_batting, g_defense
FROM appearances
WHERE playerID LIKE 'a%'
ORDER BY playerID;

Output

+----+-------------+-------------+------------+-------+--------------------+--------------------+---------+------+------+----------+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------------+------------+-------+--------------------+--------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | appearances | NULL       | range | NC_playerid_g_cols | NC_playerid_g_cols | 38      | NULL | 3282 |   100.00 | Using where; Using index |
+----+-------------+-------------+------------+-------+--------------------+--------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Dari penambahan non-clustered index pada table appearances kita dapat perubahan informasi dari query Select diatas. kita tidak lagi menggunakan filesort dan temporary table, yang membuat query kita lebih efesien dari sebelum menggunakan index.

Berikut adalah hal yang penting dicatat tentang performa dari query

1. Hindari penggunakan wildcard (% and _) diawal pencari setelah klausa LIKE

2. Wildcard underscore (_) lebih disarankan digunakan dalam pertimbangan performa dari pada wildcard percentage (%)

Terakhir yang akan kita basah adalah menampilkan grafik (menggunakan MySql Workbrench) atas query yang dijalankan. Kita akan menjalankan query sebelumnya tapi tanpa menggunakan clausa EXPLAIN.

Ada dua cara untuk menampilkan grafik dari sebuah query;

1. Pilih tab Query lalu pilih Explain Current Statement

2. Menggunakan shortcut Ctrl + Alt + X

Jika tidak ada error pada query maka MySql Workbrench akan menampilkan grafik tepat seperti dibawha ini

Gambar diatas menjelaskan bahwa query tersebut menggunakan NC_playerid_g_cols index yang mana hanya menge-scan range dari index (cukup bagus, karena query tidak menge-scan keseluruhan index). Perkiraan dari query tersebut terdapat 3.28 rb baris yang discan dan waktu 696.42 milliseconds. Gambar tersebut juga menampilkan query tersebut dijalankan pada table appearances, lalu proses scan dilanjutkan menge-cechk nilai yang berbeda (DISTINCT) lalu mengurutkan (ORDER) setelah itu baru hasil dari query tersebut.

Oke, sekarang bagaimana kalau kita tidak menggunakan index, apa yang akan ditampilkan sebagaimana visualisasi diatas ? Sebelum itu kita harus menghapus index NC_playerid_g_cols.

ALTER TABLE appearances DROP INDEX NC_playerid_g_cols;

Oke, index tersebut sudah terhapus sekarang coba kita visualkan kembali query select diatas dengan menampilkan grafik query tersebut.

Euw, dari visual diatas, dari yang sebelumnya Sql melakukan scan hanya 3.2 rb menjadi 10 rb, cukup banyak proses yang tidak efisien, serta waktu yang tadinya membutuhkan 6.. millisecond menjad 10 millisecond. Serta, coba perhatikan Query Cost yang menggunakan non-clustered index memakan waktu 696 millisecond, angka tersebut meningkat secara drastis menjadi 22434 millisecond.

Query Cost dihitung berdasarkan sebarapa banyak CPU atau I/O MySql perkirakaan yang dibutuhkan. Jadi angka tersebut menggambarkan keuntungan menggunakan index dalam menjalan query.