Querying Multiple Tables
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.
Understanding JOIN
JOIN adalah proses menghubungkan dua atau lebih tabel dalam satu query tunggal. Menggabungkan tabel-tabel dalam sebuah query mewajibkan anda untuk menggabungkan mereka pada kolom yang berhubungan atau memiliki relasi pada setiap tabel yang ingin digabungkan. Ada beberapa pasanga tipe join, diantaranya.
-
Inner Join : Tipe ini, mengembalikan hanya record yang sesuai dari setiap table yang digabungkan.
-
Outer Join : Tipe ini memeliki beberapa join yang dapat digunakan, sebegai berikut;
-
Left Outer Join : Tipe join ini mengembalikan semua baris dari tabel kiri serta baris yang cocok antara tabel kiri dan kanan.
-
Right Outer Join : Tipe join ini mengembalikan semua baris dari tabel kanan dan baris yang cocok antara kanan dan kiri.
-
Full Outer Join : Tipe join ini mengembalikan semua baris dari kiri dan kanan, tapi tipe ini tidak tersedia di MySql.
-
-
Cross Join : Tipe join ini mengembalikan sebuah kombinasi setiap baris dari dua tabel, meshhupp form me lol.
-
Natural Join : Tipe dari join ini akan mengaitkan kolom-kolom dengan nama yang sama pada satu sama lain table-table yang di-join. Ini sama dengan inner join atau left outer join, namun tidak harus memperjelas kolom-kolom yang digabung.
-
Self Join : Tipe join ini digunakan untuk mengabungkan sebuah tabel itu sendiri.
Understanding results returned with an inner join
Venn diagram dibawah ini menggambarkan record-record yang dikembalikan jikn menggabungkan table A dan B menggunakan tipe inner join.
Diagram diatas menggambarkan hanya nilai yang cocok diantara kedua gabungan tabel tersebut yang dikembalikan oleh tipe inner join.
Understanding results returned with a left outer join
Left outer join mengembalikan tabel kiri dan baris yang cocok dengan table kiri dan table kanan.
Jika tidak ada baris pada table B yang cocok dengan baris pada table A, maka tipe ini hanya mengembalikan record pada table A. Jika tidak ada dan baris yang dikembalikan pada table A yang tidak memliki kecocokan dengan baris pada tabel B akan menampilkan nilai null untuk kolom pada tabel B.
Selain itu, anda dapat menggunakan left excluding join. Dengan ini, tipe ini akan mengembalikan semua baris pada tabel kiri namun tidak termasuk baris yang cocok dengan tabel kanan. Penggambaran ini ada pada gambar dibawah.
Understanding results returned with a right outer join
Right outer join mengembalikan tabel kanan dan baris yang cocok antara tabel kanan dan tabel kiri.
Jika tidak baris pada tabel A yang cocok dengan baris pada table B maka tipe join ini akan mengembalikan baris pada table B saja. Dan baris pada tabel B yang tidak ada baris yang cocok dengan tabel akan akan menampilkan nilai null pada kolom di table B.
Sama denga left outer join, tipe ini juga memliki right excluding join.
Using Inner JOIN
Jika anda bermaksud menampilkan baris yang hanya cocok diantara tabel maka gunakanlah INNER JOIN.
Learning INNER JOIN syntax
Untuk menggabungkan tabel menggunakan INNER JOIN gunakan syntax dibawah ini (standar using of inner join)
SELECT column(s)
FROM table_1
INNER JOIN table_2
ON table_1.column = table_2.column
WHERE conditions
ORDER BY column(s)
Query diatas hanya akan mengembalikan baris yang cocok antara table_1 dan table_2.
Oke, mudahnya, kita akan gunakan langsung pada database lahmansbaseballdb untuk men-join tabel-tabel didalamnya. Misalkan, kita ingin men-join tabel dari appearances dan people.
SELECT people.nameFirst, people.nameLast, appearances.playerID, appearances.G_all
FROM appearances
INNER JOIN people
ON appearances.playerID = people.playerID;
Output Query dan Explain
+--------------+----------------+-----------+-------+
| nameFirst | nameLast | playerID | G_all |
+--------------+----------------+-----------+-------+
| David | Aardsma | aardsda01 | 1 |
| David | Aardsma | aardsda01 | 11 |
| David | Aardsma | aardsda01 | 25 |
| David | Aardsma | aardsda01 | 33 |
| David | Aardsma | aardsda01 | 43 |
| ................................................. |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-----------------------------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-----------------------------------+-------+----------+--------------------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 19554 | 100.00 | Using where |
| 1 | SIMPLE | appearances | NULL | ref | NC_playerid_g_cols | NC_playerid_g_cols | 38 | lahmansbaseballdb.people.playerID | 5 | 100.00 | Using where; Using index |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-----------------------------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
Tidak sebatas dua table, join tabel dapat kita lakukan pada lebih dari dua tabel. Kasus ini kita ingin meng-query tiga table, appearances, people dan batting.
SELECT people.playerID, people.birthYear, appearances.yearID, appearances.teamID, appearances.G_defense, batting.H
FROM appearances
INNER JOIN people
ON appearances.playerID = people.playerID
INNER JOIN batting
ON people.playerID = batting.playerID
WHERE batting.yearid = 2017 AND H <> 0
ORDER BY people.playerID, appearances.yearID, appearances.teamID, appearances.G_defense, batting.H;
Output
+-----------+-----------+--------+--------+-----------+------+
| playerID | birthYear | yearID | teamID | G_defense | H |
+-----------+-----------+--------+--------+-----------+------+
| abreujo02 | 1987 | 2014 | CHA | 109 | 189 |
| abreujo02 | 1987 | 2015 | CHA | 115 | 189 |
| abreujo02 | 1987 | 2016 | CHA | 152 | 189 |
| abreujo02 | 1987 | 2017 | CHA | 139 | 189 |
| abreujo02 | 1987 | 2018 | CHA | 114 | 189 |
| adamsla01 | 1989 | 2014 | KCA | 2 | 30 |
| adamsla01 | 1989 | 2017 | ATL | 41 | 30 |
| .......................................................... |
Diagram dibawah ini menggambarkan baris yang dikembalikan atas query diatas
Learning how to use table aliases
Fitur aliases membuat kita dapat memberikan nama pendek pada nama kolom didalam query. Manfaatnya penulisan nama kolom atau table menjadi lebih singkat dengan dibuatnya alias ini. Berikut adalah penggunakan aliases menggunakan keyword AS pada query join 3 tabel.
SELECT B.playerID, B.birthYear, A.yearID, A.teamID, A.G_defense, C.H
FROM appearances AS A
INNER JOIN people AS B
ON A.playerID = B.playerID
INNER JOIN batting AS C
ON B.playerID = C.playerID
WHERE C.yearid = 2017 AND H <> 0
ORDER BY B.playerID, A.yearID, A.teamID, A.G_defense, C.H;
Using OUTER JOIN
Lerning LEFT OUTER JOIN syntax
Untuk menggunakan tipe join ini gunakan syntax berikut
SELECT column(s)
FROM table_1
INNER JOIN table_2
ON table_1.column = table_2.column
WHERE conditions
ORDER BY column(s)
Query diatas akan mengembalikan seluruh baris pada tabel_1 dan baris pada tabel_2 yang cocok dengan table_1
Hmm oke kita coba langsung penerapannya. Anda diminta untuk menampilkan pemain dengan kelahiran 1985, serta beberapa informasi tentang pemain tersebut ketika bermain di sekolah mereka.
SELECT p.playerID, p.nameGiven, p.birthYear, c.schoolID, c.yearID
FROM people p
LEFT OUTER JOIN collegeplaying c
ON p.playerID = c.playerID
WHERE p.birthYear = 1985;
Output
+-----------+-----------------------+-----------+------------+--------+
| playerID | nameGiven | birthYear | schoolID | yearID |
+-----------+-----------------------+-----------+------------+--------+
| abadfe01 | Fernando Antonio | 1985 | NULL | NULL |
| abreuju01 | Juan de Dios | 1985 | NULL | NULL |
| adducji02 | Jim Charles | 1985 | NULL | NULL |
| alberan01 | Andrew William | 1985 | kentucky | 2005 |
| alberan01 | Andrew William | 1985 | kentucky | 2006 |
| alberan01 | Andrew William | 1985 | kentucky | 2007 |
Sebagaiman yang kita lihat diatas, ada beberapa baris yang menampilkan nilai NULL, artinya, playerID pada tabel collageplaying tidak ada yang cocok dengan playerid pada tabel people.
Sama dengna inner join, outer join juga dapat menggabungkan lebih dari dua buah tabel. Dari query terakhir diaras mari kita tambah dengan menampilkan kolom yearid dan gameid pada tabel allstarfull.
SELECT p.playerID, p.nameGiven, p.birthYear, c.schoolID, c.yearID
FROM people AS p
LEFT OUTER JOIN collegeplaying AS c
ON p.playerID = c.playerID
LEFT OUTER JOIN allstarfull AS asf
ON c.playerID = asf.playerID
WHERE p.birthYear = 1985;
Output
+-----------+-----------------------+-----------+------------+--------+
| playerID | nameGiven | birthYear | schoolID | yearID |
+-----------+-----------------------+-----------+------------+--------+
| abadfe01 | Fernando Antonio | 1985 | NULL | NULL |
| abreuju01 | Juan de Dios | 1985 | NULL | NULL |
| adducji02 | Jim Charles | 1985 | NULL | NULL |
| alberan01 | Andrew William | 1985 | kentucky | 2005 |
| alberan01 | Andrew William | 1985 | kentucky | 2006 |
| alberan01 | Andrew William | 1985 | kentucky | 2007 |
Selain itu, anda bisa menggunakan excluding join, jadi, query tersebut akan mengembalikan semua baris yang tidak ada kecocokan dengan baris pada bagian kanannya, nilai pengembaliannya akan seperti ini
Untuk excluding join anda dapat menggunakan syntax dibawah ini
SELECT column(s)
FROM table_1
LEFT OUTER JOIN table_2
ON table_1.column = table_2.column
WHERE table2.column IS NULL;
Yup, kita hanya menambah kondisi dimana pada kolom pada table_2 bernilai Null. Ingat, setiap baris pada table yang ke-2 akan bernilai null jika tidak ada baris yang cocok dengan tabel utama.
Sekarang mari kita excluding join pada query outer join 3 tabel terakhir kita
SELECT p.playerID, p.nameGiven, p.birthYear, c.schoolID, c.yearID
FROM people AS p
LEFT OUTER JOIN collegeplaying AS c
ON p.playerID = c.playerID
LEFT OUTER JOIN allstarfull AS asf
ON c.playerID = asf.playerID
WHERE p.birthYear = 1985 AND asf.playerID IS NULL AND c.playerID IS NULL;
Output
+-----------+-----------------------+-----------+----------+--------+
| playerID | nameGiven | birthYear | schoolID | yearID |
+-----------+-----------------------+-----------+----------+--------+
| abadfe01 | Fernando Antonio | 1985 | NULL | NULL |
| abreuju01 | Juan de Dios | 1985 | NULL | NULL |
| adducji02 | Jim Charles | 1985 | NULL | NULL |
| ascanjo01 | Jose Eleazar | 1985 | NULL | NULL |
| atilalu01 | Luis A. | 1985 | NULL | NULL |
Learning RIGHT OUTER JOIN syntax
Untuk menggunakan tipe join ini gunakan syntax berikut
SELECT column(s)
FROM table_1
RIGHT OUTER JOIN table_2
ON table_1.column = table_2.column
WHERE conditions
ORDER BY column(s)
Query diatas akan mengambil semua baris pada table 2 dan baris pada tabel 1 yang cocok dengan baris pada table 2.
Mari kita buat query untuk melihat hasil dari RIGHT OUTER JOIN
SELECT p.playerid, asf.yearid, asf.gameid, asf.startingpos
FROM lahmansbaseballdb.allstarfull asf
RIGHT OUTER JOIN lahmansbaseballdb.people p
ON p.playerid = asf.playerid;
Output
+-----------+--------+--------------+-------------+
| playerid | yearid | gameid | startingpos |
+-----------+--------+--------------+-------------+
| aardsda01 | NULL | NULL | NULL |
| aaronha01 | 1955 | NLS195507120 | NULL |
| aaronha01 | 1956 | ALS195607100 | NULL |
| aaronha01 | 1957 | NLS195707090 | 9 |
| aaronha01 | 1958 | ALS195807080 | 9 |
| aaronha01 | 1959 | NLS195907070 | 9 |
Ya, query diatas mengambil seluruh baris pada tabel people dan baris yang cocok pada tabel allstarfull dengan baris pada table people.
Sama hal-nya dengan LEFT OUTER JOIN, RIGHT OUTER JOIN dapat menggabungkan lebih dari dua table dan exlcude join menggunalan kalusa WHERE dan IS NULL.
Using Advanced Joins
MySQL memliki joins lanjutan seperti cross, natural, dan self joins
Understanding what is CROSS JOIN and how to use it
A Cross JOIN mirip seperti INNER JOIN namun tidak menggunakan klausa ON. Hasil dari tipe ini seperti meng-kalikan setiap tabel dengan tabel lain, hasilnya seperti perkalian cartesian. Tipe ini akan mengembalikan sebuah kombinasi dari setiap baris dari dua buah tabel. Penggabungakan ini menghasilkan banyak baris. Mungkin hasilnya tidak muncul karena terlalu intensif bagi database system untuk mengembalikan hasil dari query menggunakna tipe JOIN ini.
Untuk menggunakan cross join gunakan syntax dibawah ini
Untuk mensimulasikan cross join kita akan coba membuat dua tabel, dan meng-join keduanya. Saya akan menggunakan schema baru agar tidak merusak keutuhan schema yang dibawakan oleh penulis buku.
CREATE SCHEMA IF NOT EXISTS adds_schema;
CREATE TABLE IF NOT EXISTS adds_schema.tabel_a (
`huruf` VARCHAR (1) NOT NULL
);
CREATE TABLE IF NOT EXISTS adds_schema.tabel_b (
`angka` INT NOT NULL
);
INSERT INTO adds_schema.tabel_a VALUE('A'), ('B'),('C'),('D');
INSERT INTO adds_schema.tabel_b VALUE(1), (2),(3),(4);
Selanjutnya mari kita corss join tabel_a dan tabel_b
select a.*, b.*, CONCAT(a.huruf,"",b.angka)
FROM tabel_a AS a
CROSS JOIN tabel_b AS b
ORDER BY a.huruf, b.angka; ---(1)!
- Hanya biar rapih saja di urutkan, tidak ada kaitannya dengan CROSS JOIN.
Output
+-------+-------+----------------------------+
| huruf | angka | CONCAT(a.huruf,"",b.angka) |
+-------+-------+----------------------------+
| A | 1 | A1 |
| A | 2 | A2 |
| A | 3 | A3 |
| A | 4 | A4 |
| B | 1 | B1 |
| B | 2 | B2 |
| B | 3 | B3 |
| B | 4 | B4 |
| C | 1 | C1 |
| C | 2 | C2 |
| C | 3 | C3 |
| C | 4 | C4 |
| D | 1 | D1 |
| D | 2 | D2 |
| D | 3 | D3 |
| D | 4 | D4 |
+-------+-------+----------------------------+
Warning
Yang harus anda ingat pada cross join adalah, tipe tersebut bisa sangat intensif bagi sistem basis data, dan baiknya gunakan tipe join lain jika memungkinkan, dan selalu gunakan klausa WHERE jika menggunakan CROSS JOIN.
Understanding What NATURAL JOIN is and how to use it
Tipe join ini akan mengaitkan kolom-kolom yang memliki nama yang sama dari tabel-tabel yang digabungkan. Hampir mirip dengan INNER JOIN atau LEFT OUTER JOIN. Untuk menggunakan NATURAL JOIN gunakan query berikut;
Marikita simulasikan dengan tabel collegePlaying dan schoolSELECT c.playerid, c.schoolid as 'School ID-collegeplaying',
c.yearid, s.schoolid as 'School ID-schools', s.city, s.state, s.country
FROM lahmansbaseballdb.collegeplaying c
NATURAL JOIN lahmansbaseballdb.schools s;
Output
+-----------+--------------------------+--------+-------------------+------------+-------+---------+
| playerid | School ID-collegeplaying | yearid | School ID-schools | city | state | country |
+-----------+--------------------------+--------+-------------------+------------+-------+---------+
| birkbmi01 | akron | 1980 | akron | Akron | OH | USA |
| birkbmi01 | akron | 1981 | akron | Akron | OH | USA |
| birkbmi01 | akron | 1982 | akron | Akron | OH | USA |
| birkbmi01 | akron | 1983 | akron | Akron | OH | USA |
| dilauja01 | akron | 1962 | akron | Akron | OH | USA |
| malasma01 | akron | 1998 | akron | Akron | OH | USA |
| malasma01 | akron | 1999 | akron | Akron | OH | USA |
| malasma01 | akron | 2000 | akron | Akron | OH | USA |
| nealejo01 | akron | 1893 | akron | Akron | OH | USA |
| avilaal01 | alabama | 2006 | alabama | Tuscaloosa | AL | USA |
Hasil tersebut akan menjadi NATRUAL JOIN, dimana tipe join ini mencari kesamaan antar kolom-kolom pada tabel schools dan collegeplaying, yang mana pada kasus ini dapat kita lihat, kolom schoolID yang menjadi kondisi kesamaan antara kedua table. Dapat dibuktikan dengan hasil diatas, School ID-collegeplaying dan School ID-schools memliki nilai yang identik.
Understanding set theory
Teori set adalah yang mendasari konsep dari SQL. Set adalah kumpulan dari objek-objek. Setiap objek didalam set disebut dengna element. Didalam MySql , tabel adalah set dan recordnya adalah element. Anda dapat mengambil subset dari set. Subset adalah set terkecil dari element pada sebuah set. Di SQL anda dapat mengambil subset menggunakan klausa WHERE. Cross product adalah sebuah set yang diciptakan dari dua buah atau lebih set. Didalam sql, cross product adalah join. Untuk membuat different set of data didalam sql, anda dapat menggunakan intersection, difference dan union joins.
Understanding what is a UNION join is and learning how to use it in a SQL query
UNION dapat mengkombinasikan dua atau lebih hasil set kedalam satu set. Namun ada beberapa aturan yang harus diikut untuk menghindari error, diantaranya:
- Banyak kolom pada statement SELECT harus sama.
- Urutan kolom-kolom yang ada pada statement SELECT harus sama.
- Tipe data dari setiap kolom-kolom harus sama atau setidaknya kompatibel.
Ada beberapa yang harus diingat dalam me-review hasil dari UNION join, diantaranya:
* Nama kolom pada kolom-kolom terakhir akan disamakan dengan nama dari kolom-kolom yang digunakan pada statement select pertama.
* GROP BY dan HAVING hanya dapat digunakan dalam setiap query, namun tidak akan berdampak pada hasil akhir
- Anda memliki dua pilihan ketika menggunakan UNION, diantranya:
UNION- tipe ini akan menghapus record yang ganda, duplicate tanpa menggunakan DISTINCT pada statement SELECT.
UNION ALL- Tipe ini tidak akan menghapus record ganda. Lalu, tipe ini lebih cepat dibandingkan dengan UNION karena tipe ini tidak perlu mengetahui record mana yang duplicate lalu menghapusnya.
Kedua UNION diatas akan mengkombinasi secara vertikal, sedangkan JOIN menggabungkannya secara horizontal. Gambar dibawah ini menjelaskan perbedaan antara JOIN, UNION, dan UNION ALL.
UNION
Untuk menggunakan UNION gunakna syntax berkut ini;
SELECT column(s)
FROM table1
WHERE condition(s)
UNION
SELECT column(s)
FROM table2
WHERE condition(s)
ORDER BY column(s)
Syntax diatas menujukan kita bagaimana cara UNION dua query bersamaan. WHERE didalam setiap query adalah opsional. ORDER BY juga opsional namun hanya dapat digunakan pada query terakhir.
Sekarang kita coba papa kasus di lahmansbaseballdb, anda diperintah untuk menampilkan penghargaan yang deterima oleh manager dan player pada tahun 1994.
SELECT p.playerID, p.nameGiven, awardID, yearID
FROM awardsplayers AS ap
INNER JOIN people AS p ON ap.playerID = p.playerID
WHERE yearID = 1994
UNION
SELECT p.playerID, p.nameGiven, awardID, yearID
FROM awardsmanagers AS am
INNER JOIN people AS p ON am.playerID = p.playerID
WHERE yearID = 1994
ORDER BY awardid;
Output
+-----------+-------------------+-------------------------------------+--------+
| playerID | nameGiven | awardID | yearID |
+-----------+-------------------+-------------------------------------+--------+
| mcgrifr01 | Frederick Stanley | All-Star Game MVP | 1994 |
| showabu99 | William Nathaniel | BBWAA Manager of the Year | 1994 |
| aloufe01 | Felipe Rojas | BBWAA Manager of the Year | 1994 |
| smithoz01 | Osborne Earl | Branch Rickey Award | 1994 |
| coneda01 | David Brian | Cy Young Award | 1994 |
| maddugr01 | Gregory Alan | Cy Young Award | 1994 |
| mattido01 | Donald Arthur | Gold Glove | 1994 |
| bondsba01 | Barry Lamar | Gold Glove | 1994 |
| rodriiv01 | Ivan | Gold Glove | 1994 |
| griffke02 | George Kenneth | Gold Glove | 1994 |
| pagnoto01 | Thomas Alan | Gold Glove | 1994 |
| grissma02 | Marquis Deon | Gold Glove | 1994 |
| alomaro01 | Roberto | Gold Glove | 1994 |
| maddugr01 | Gregory Alan | Gold Glove | 1994 |
| loftoke01 | Kenneth | Gold Glove | 1994 |
| lewisda01 | Darren Joel | Gold Glove | 1994 |
Sekarang kita coba untuk membuat SELECT statement tidak sesuai jumlahnya pada UNION diatas. Kita akan kolom yearID pada SELECT yang ke-2.
SELECT p.playerID, p.nameGiven, awardID, yearID
FROM awardsplayers AS ap
INNER JOIN people AS p ON ap.playerID = p.playerID
WHERE yearID = 1994
UNION
SELECT p.playerID, p.nameGiven, awardID ---(1)!
FROM awardsmanagers AS am
INNER JOIN people AS p ON am.playerID = p.playerID
WHERE yearID = 1994
ORDER BY awardid;
- Menghapus kolom
yearID
Jika banyak kolom pada setiap statement tidak sama maka MySql akan menghasilkan error. Sebagiamana syarat yang harus dipenuhi (baca. Bagaimana jika urutan dari query tidak seusai pada setiap SELECT statement ?
SELECT p.playerID, p.nameGiven, awardID, yearID
FROM awardsplayers AS ap
INNER JOIN people AS p ON ap.playerID = p.playerID
WHERE yearID = 1994
UNION
SELECT p.playerID, p.nameGiven, yearID, awardID
FROM awardsmanagers AS am
INNER JOIN people AS p ON am.playerID = p.playerID
WHERE yearID = 1994
ORDER BY awardid;
Output
| playerID | nameGiven | awardID | yearID |
+-----------+-------------------+-------------------------------------+---------------------------+
| showabu99 | William Nathaniel | 1994 | TSN Manager of the Year |
| showabu99 | William Nathaniel | 1994 | BBWAA Manager of the Year |
| aloufe01 | Felipe Rojas | 1994 | TSN Manager of the Year |
| aloufe01 | Felipe Rojas | 1994 | BBWAA Manager of the Year |
| mcgrifr01 | Frederick Stanley | All-Star Game MVP | 1994 |
| smithoz01 | Osborne Earl | Branch Rickey Award | 1994 |
| coneda01 | David Brian | Cy Young Award | 1994 |
| maddugr01 | Gregory Alan | Cy Young Award | 1994 |
| maddugr01 | Gregory Alan | Gold Glove | 1994 |
| griffke02 | George Kenneth | Gold Glove | 1994 |
| grissma02 | Marquis Deon | Gold Glove | 1994 |
Kita tidak mendapatkan sebuah error, query tersebut memberikan hasil diatas. Query diatas tidak memliki urutan yang benar. Kolom awardid dan yearid ditukar. Nama kolom yang digunakan adalah namakolom pada SELECT statement pertama. Namun, bukan berarti jika MySql tidak menghasilkan error query kita bekerja sesuai yang diharapkan, tidak selalu. Kasus diatas MySql mengkonversi secara implisit yearid ke kolom yang memliki tipe sejenis seperti awardid.
Berbicara tentang konversi, terdapat dua tipe konversi, explicit dan implicit. Explicit conversion maksudnya adalah ketika secara tersurat anda mengganti tipe data. Sedangkan Implicit terjadi ketika MySql harus me-nyamakan tipe data, sama seperti kasus diatas ketika kita menggunakan UNION JOIN, mengkonversi SMALLINT kedalam VARCHAR dan sebaliknya.
Sebagai tambahan anda dapat membuat kolom statis dengan nilai yang statis, pada contoh ini kita diperintah untuk membuat kolom yang menjelaskan baris-baris tersebut player atau manager.
SELECT p.playerID, p.nameGiven, awardID, yearID, 'Player' AS playeridType
FROM awardsplayers AS ap
INNER JOIN people AS p ON ap.playerID = p.playerID
WHERE yearID = 1994
UNION
SELECT p.playerID, p.nameGiven, awardID, yearID, 'Manager' AS palyeridType
FROM awardsmanagers AS am
INNER JOIN people AS p ON am.playerID = p.playerID
WHERE yearID = 1994
ORDER BY awardid;
Output
+-----------+-------------------+-------------------------------------+--------+--------------+
| playerID | nameGiven | awardID | yearID | playeridType |
+-----------+-------------------+-------------------------------------+--------+--------------+
| mcgrifr01 | Frederick Stanley | All-Star Game MVP | 1994 | Player |
| showabu99 | William Nathaniel | BBWAA Manager of the Year | 1994 | Manager |
| aloufe01 | Felipe Rojas | BBWAA Manager of the Year | 1994 | Manager |
| smithoz01 | Osborne Earl | Branch Rickey Award | 1994 | Player |
| coneda01 | David Brian | Cy Young Award | 1994 | Player |
| maddugr01 | Gregory Alan | Cy Young Award | 1994 | Player |
| mattido01 | Donald Arthur | Gold Glove | 1994 | Player |
| bondsba01 | Barry Lamar | Gold Glove | 1994 | Player |
| rodriiv01 | Ivan | Gold Glove | 1994 | Player |
| griffke02 | George Kenneth | Gold Glove | 1994 | Player |
| pagnoto01 | Thomas Alan | Gold Glove | 1994 | Player |
| grissma02 | Marquis Deon | Gold Glove | 1994 | Player |
| alomaro01 | Roberto | Gold Glove | 1994 | Player |
| maddugr01 | Gregory Alan | Gold Glove | 1994 | Player |
| loftoke01 | Kenneth | Gold Glove | 1994 | Player |
| lewisda01 | Darren Joel | Gold Glove | 1994 | Player |
| langsma01 | Mark Edward | Gold Glove | 1994 | Player |
| larkiba01 | Barry Louis | Gold Glove | 1994 | Player |
| boggswa01 | Wade Anthony | Gold Glove | 1994 | Player |
UNION ALL
Jika anda menjalan query sebelumnya dan mengganti dari UNION menjadi UNION ALL, anda akan mendapatkan hasil yang sama karena tidak ada record ganda untuk di filter.
Gunakan query dibawah ini untuk mengatahu bagaimana UNION ALL bekerja.
SELECT playerid, yearid, teamid, G AS gamesbatted FROM
lahmansbaseballdb.batting
WHERE yearid = 2005
UNION ALL
SELECT playerid, yearid, teamid, g_batting FROM
lahmansbaseballdb.appearances
WHERE yearid = 2005
ORDER BY yearid, playerid, gamesbatted;
Output
+-----------+--------+--------+-------------+
| playerid | yearid | teamid | gamesbatted |
+-----------+--------+--------+-------------+
| abernbr01 | 2005 | MIN | 24 |
| abernbr01 | 2005 | MIN | 24 |
| abreubo01 | 2005 | PHI | 162 |
| abreubo01 | 2005 | PHI | 162 |
| accarje01 | 2005 | SFN | 28 |
| accarje01 | 2005 | SFN | 28 |
| acevejo01 | 2005 | COL | 36 |
| acevejo01 | 2005 | COL | 36 |
| adamsmi03 | 2005 | MIL | 12 |
| adamsmi03 | 2005 | MIL | 13 |
| adamsru01 | 2005 | TOR | 139 |
| adamsru01 | 2005 | TOR | 139 |
| adamste01 | 2005 | PHI | 14 |
| adamste01 | 2005 | PHI | 16 |
| adkinjo01 | 2005 | CHA | 0 |
| adkinjo01 | 2005 | CHA | 5 |
| affelje01 | 2005 | KCA | 3 |
| affelje01 | 2005 | KCA | 49 |
| aguilch01 | 2005 | FLO | 65 |
| aguilch01 | 2005 | FLO | 65 |
Dari Output diatas kita dapat mengoservasi;
- Hasil dari query tersebut, anda dapat lihat setiap plyerId selalu memliki dua baris tanpa memperhatikan apakah
yearId,teamIddangamesbatted fieldspada kedua baris tersebut sama atau tidak pada tabel batting dan appearances - Diatas menggunaka UNION ALL, jika kita menggunakan query diatas dan menggunakan UNION maka seharusnya setiap playerID hanya mengeluarkan satu baris jika
yearId,teamIddangamesbatted fieldsmemiliki nilai yang berbeda.
SELECT playerid, yearid, teamid, G AS gamesbatted
FROM lahmansbaseballdb.batting
WHERE yearid = 2005
UNION
SELECT playerid, yearid, teamid, g_batting
FROM lahmansbaseballdb.appearances
WHERE yearid = 2005
ORDER BY yearid, playerid, gamesbatted;
Output
+-----------+--------+--------+-------------+
| playerid | yearid | teamid | gamesbatted |
+-----------+--------+--------+-------------+
| abernbr01 | 2005 | MIN | 24 |
| abreubo01 | 2005 | PHI | 162 |
| accarje01 | 2005 | SFN | 28 |
| acevejo01 | 2005 | COL | 36 |
| adamsmi03 | 2005 | MIL | 12 |
| adamsmi03 | 2005 | MIL | 13 |
| adamsru01 | 2005 | TOR | 139 |
| adamste01 | 2005 | PHI | 14 |
| adamste01 | 2005 | PHI | 16 |
| adkinjo01 | 2005 | CHA | 0 |
| adkinjo01 | 2005 | CHA | 5 |
Higlight diatas, untuk playerID adamsmi03 tetap memliki dua baris karena nilai pada kolom gamesbatted memliki nilai yang berbeda pada tabel gamesbatted dan appearances.
Understanding what an intersect is and learning how to use it in a SQL query
Intersect dapat mengkombinasi dua atau lebih sets yang mengandung nilai yang berbeda dari setiap set. Foto dibawah ini menggambarkan hasil dari sebuah intersect:
MySql tidak mendungkun INTERSECT SQL operator, namun ada workaround menggunakan join. Anda dapat menambahkan DISTINCT pada INNER JOIN. Query dibawah ini menunjukan cara meng-intersect dalam MySql.
Tabel simulasi INTERSECT
CREATE SCHEMA IF NOT EXISTS adds_schema;
CREATE TABLE IF NOT EXISTS adds_schema.tabel_satu (
`id` INT NOT NULL PRIMARY KEY,
`huruf` VARCHAR (1) NOT NULL
);
CREATE TABLE IF NOT EXISTS adds_schema.tabel_dua (
`id` INT NOT NULL PRIMARY KEY,
`huruf` VARCHAR (1) NOT NULL
);
INSERT INTO adds_schema.tabel_satu VALUE(1,'A'), (2,'A'),(3,'C'),(4,'D'),(5,'F'),(6,'C');
INSERT INTO adds_schema.tabel_dua VALUE(1,'A'), (2,'A'),(3,'C'),(4,'E'),(5,'B'),(6,'C');
select * from tabel_satu; SELECT * FROM tabel_dua;
+----+-------+
| id | huruf |
+----+-------+
| 1 | A |
| 2 | A |
| 3 | C |
| 4 | D |
| 5 | F |
| 6 | C |
+----+-------+
6 rows in set (0,00 sec)
+----+-------+
| id | huruf |
+----+-------+
| 1 | A |
| 2 | A |
| 3 | C |
| 4 | E |
| 5 | B |
| 6 | C |
+----+-------+
6 rows in set (0,01 sec)
Jalankan query dibawah ini untuk meng-INTERSECT tabel_satu dan tabel_dua
USE adds_schema;
SELECT DISTINCT b.huruf
FROM tabel_satu a
INNER JOIN tabel_dua b ON b.huruf = a.huruf;
Hasil diatas adalah intersection dari tabel_satu dan tabel_dua dibawah kolom huruf. Daftar dari hasil diatas mengandung hanya nilai yang ada dikedua tabel tersebut. Operator DISTINCT menghapus nilai yang ganda, dan inner join mengembalikan baris dari kedua tabel.
Using indexes with your query
Pada sesi ini, kita akan melihat index mana yang kita gunakan pada query kita dan bagaimana men-troubleshoot index pada query tersebut jika performa tidak sesuai dengan yang kitaharpakan, dalam arti, LAMBAT. Untuk memulai kita gunakan query dibawah dengan menambahkan klausa EXPLAIN.
EXPLAIN SELECT p.playerid, p.birthyear,
a.yearid, a.teamid, a.G_defense, b.H
FROM lahmansbaseballdb.appearances AS a
INNER JOIN lahmansbaseballdb.people AS p
ON p.playerid = a.playerid
INNER JOIN lahmansbaseballdb.batting AS b
ON p.playerid = b.playerid
WHERE b.yearid = 2017
AND b.H <> 0
ORDER BY p.playerid, a.yearid, a.teamid, a.G_defense, b.H;
Output
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 19484 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 40 | lahmansbaseballdb.p.playerID,const | 1 | 90.00 | Using where |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 105627 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
3 rows in set, 1 warning (0,03 sec)
Dari hasil siatas dapat kita lihat bahwa;
-
Pada kolom table, a adalah alias dari tabel
appearances, dari p adalah alias dari tabelpeople, dan b alias dari tabelbatting. Jika kita tidak menggunakan alias, maka nama asli tabel yang akan ditampilkan -
Tabel
batting, b, menggunakan primary key dalam mengembalikan datanya, Ini baguas, karena hasil yang didapatkan akan lebih cepat karena query menggunakan index. Tabelbattingdifilter sebesar 90.00 menggunakan klausa where. -
Tabel
peopledanappearancestidak menggunakan index pada query tersebut. Tabelpeoplemenggunakan temporary dan filesort yang mana memperlambat query, dan tabelappearancesmenggunakan join buffer (Block Nested Loop) yang mana artinya data dari yang dari awal digabung telah ditempatkan kedalam sebuah buffer. Jadi data dari tableappearancseakan digabungkan pada data tersebut didalam buffer, yang menyebabkan query tersebut lebih lambat. -
Query tersebut tanpa menggunakan klausa
EXPLAINakan mengembalikan sebanyak 5520 baris, namun ternyata query tersebut memindai sebanyak 19.554 baris pada tabelpeopledan 105.267 baris pada tabelappearances. -
Kolom Type menampilkan nilai yang berbeda, All dan ref. Kolom tersebut menggambarkan tipe join yang digunakan untuk tabel. All artinya terjadi pemindaian secara menyeluruh pada tabel, yup, jika kita lihat pada tabel
peopledanappearancestidak memliki index, sehingga perlu untuk memindai seluruh tabel untuk menemukan hasil. Sedangkan ref artinya semua baris yang memliki kecocokan yang ada pada tabel lain yang akan dibaca, seperti tabelbattingyang memliki index, sehingga query tidak perlu untuk memindai secara menyeluruh.
Sebelum menambahkan index untuk meningkatkan performa sebuah query, anda harus melihat query anda terlebih dahulu. Misalkan, pada contoh diatas, penggunakan klausa WHERE merujuk pada kolom yearID pada tabel batting. Coba anda ganti menggunakan tabel appearances.
EXPLAIN SELECT p.playerid, p.birthyear,
a.yearid, a.teamid, a.G_defense, b.H
FROM lahmansbaseballdb.appearances AS a
INNER JOIN lahmansbaseballdb.people AS p
ON p.playerid = a.playerid
INNER JOIN lahmansbaseballdb.batting AS b
ON p.playerid = b.playerid
WHERE a.yearid = 2017 ---(1)!
AND b.H <> 0
ORDER BY p.playerid, a.yearid, a.teamid, a.G_defense, b.H;
- Mengganti
WHERE b.yearid = 2017keWHERE a.yearid = 2017
Output
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------+-------+----------+--------------------------------------------+
| 1 | SIMPLE | a | NULL | ref | PRIMARY | PRIMARY | 2 | const | 1494 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 19484 | 10.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 38 | lahmansbaseballdb.p.playerID | 5 | 90.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------+-------+----------+--------------------------------------------+
3 rows in set, 1 warning (0,00 sec)
Dari hasil diatas, sekarang, table a (appearances) menggunakan PRIMARY KEY dan rows yang dipindai menjadi 1.494 baris, jauh lebih kecil yang awalnya 19.554 baris. Namun, query ini akan mengmbalikan jumlah baris 5602 yang dari sebelumnya 5520. Ya, lebih cepat, namun bisa jadi perubahan ini tidak sesuai dengan yang anda inginkan.
Info
Jumlah baris diketahui dengan cara menjalankan query tersebut tanpa klausa EXPLAIN dan lihat pada bagian yang paling bawah. Misalkan
| zobribe01 | 1981 | 2018 | CHN | 125 | 101 |
| zuninmi01 | 1991 | 2013 | SEA | 50 | 97 |
| zuninmi01 | 1991 | 2014 | SEA | 130 | 97 |
| zuninmi01 | 1991 | 2015 | SEA | 112 | 97 |
| zuninmi01 | 1991 | 2016 | SEA | 52 | 97 |
| zuninmi01 | 1991 | 2017 | SEA | 120 | 97 |
| zuninmi01 | 1991 | 2018 | SEA | 111 | 97 |
+-----------+-----------+--------+--------+-----------+------+
5520 rows in set (0,14 sec)
| zuninmi01 | 1991 | 2017 | SEA | 120 | 61 |
| zuninmi01 | 1991 | 2017 | SEA | 120 | 75 |
| zuninmi01 | 1991 | 2017 | SEA | 120 | 87 |
| zuninmi01 | 1991 | 2017 | SEA | 120 | 97 |
+-----------+-----------+--------+--------+-----------+------+
5602 rows in set (0,05 sec)
Oke, jika demikian mari kita bermain dengan index. Sebelum itu kita harus mengetahui index yang dimiliki oleh tabel yang terikat pada query tersebut. Kita bisa menggunakan syntax SHOW INDEX FROM ‘table name’.
Output
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 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 | 2878 | NULL | NULL | | BTREE | | | YES | NULL |
| appearances | 0 | PRIMARY | 3 | playerID | A | 105627 | NULL | NULL | | BTREE | | | YES | NULL |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0,26 sec)
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| batting | 0 | PRIMARY | 1 | playerID | A | 20007 | NULL | NULL | | BTREE | | | YES | NULL |
| batting | 0 | PRIMARY | 2 | yearID | A | 97798 | NULL | NULL | | BTREE | | | YES | NULL |
| batting | 0 | PRIMARY | 3 | stint | A | 105420 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0,34 sec)
Gambar diatas menampilkan index yang ada pada tabel appearances dan batting. Sedangkan untuk tabel people tidak memliki index.
Yang akan kita coba sekarang untuk meningkat query reading ini adalah membuat Non Clustered index pada tabel appearances dan tabel people, mengapa pada kedua tabel ini ditentukan ? Lihat pada EXPLAIN query sebelumnya. Kedua tabel ini melakukan full scan alih-alih seharusnya memindai hanya tabel yang saling ada kecocokan dengan tabel yang lainnya.
Warning
Namun yang harus menjadi perhatian adalah, penambahan index ini akan memperlambat query lainnya seperti inserting, updating atau deleteing dan munkin juga akan berdampat pada menurunnya performa selecting data pada query yang lain. Maka berhati-hatilkan dalam membuat index baru untuk mempercepat sebuah query. Anda harus menganalisa dan memperhatikan query anda yang lain pada MySql Server untuk memahami dampak yang dihasilkan dari penambahan atau pengurangan sebuah index.
Karena pada query tersebut kita ingin menampilkan playerID dan birthYear pada tabel people, kita dapat menambahkan Non-Clustered untuk kedua kolom tersebut pada tabel poeple.
ALTER TABLE people
ADD INDEX NC_peopleID_birthYear (playerID ASC, birthYear ASC) VISIBLE;
Show index tabel people
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| people | 1 | NC_peopleID_birthYear | 1 | playerID | A | 19484 | NULL | NULL | YES | BTREE | | | YES | NULL |
| people | 1 | NC_peopleID_birthYear | 2 | birthYear | A | 19484 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0,16 sec)
Oke alhamdulilah kita telah berhasil menambahkan sebuah index pada tabel people. Sekarang mari kita coba lagi jalankan query dibawah ini, apakah akan ada perubahan setelah kita menambahkan index.
Perubahan performa setelah penambahan index
EXPLAIN SELECT p.playerid, p.birthyear,
a.yearid, a.teamid, a.G_defense, b.H
FROM lahmansbaseballdb.appearances AS a
INNER JOIN lahmansbaseballdb.people AS p
ON p.playerid = a.playerid
INNER JOIN lahmansbaseballdb.batting AS b
ON p.playerid = b.playerid
WHERE b.yearid = 2017
AND b.H <> 0
ORDER BY p.playerid, a.yearid, a.teamid, a.G_defense, b.H;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+------------------------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+------------------------------------+--------+----------+---------------------------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 105627 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | p | NULL | ref | NC_peopleID_birthYear | NC_peopleID_birthYear | 1023 | lahmansbaseballdb.a.playerID | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 40 | lahmansbaseballdb.p.playerID,const | 1 | 90.00 | Using where |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+------------------------------------+--------+----------+---------------------------------+
3 rows in set, 1 warning (0,01 sec)
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 19484 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 40 | lahmansbaseballdb.p.playerID,const | 1 | 90.00 | Using where |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 105627 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
3 rows
Sebagaimana yang anda lihat pada gambar diatas, query kita sekarang menggunakan non-clustered index yang baru saja kita buat pada tabel people yang mana setelah penambahan index ini MySql hanya memindai satu baris saja dari yang sebelumnya 19.554 baris (Lihat pada kolom rows). Oke sekarang mari kita coba pengoprekan pada tabel appearances, karena tabel tersebut masih harus memindai 105.627 barus dan tidak menggunakan index.
Sekarang tambah Non-Clustered index pada tabel appearances
ALTER TABLE appearances
ADD INDEX NC_yearID_teamID_GDefense (yearID ASC, teamID ASC, G_defense ASC) VISIBLE;
Show index table appearances
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 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 | 2878 | NULL | NULL | | BTREE | | | YES | NULL |
| appearances | 0 | PRIMARY | 3 | playerID | A | 105627 | NULL | NULL | | BTREE | | | YES | NULL |
| appearances | 1 | NC_yearID_teamID_GDefense | 1 | yearID | A | 171 | NULL | NULL | | BTREE | | | YES | NULL |
| appearances | 1 | NC_yearID_teamID_GDefense | 2 | teamID | A | 2946 | NULL | NULL | | BTREE | | | YES | NULL |
| appearances | 1 | NC_yearID_teamID_GDefense | 3 | G_defense | A | 81038 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0,20 sec)
Oke,sekarag kita jalankan lagi query sebelumnya untuk meliha setelah ada penambahan non-clustered index pada tabel appearances.
Perubahan performa setelah penambahan index
EXPLAIN SELECT p.playerid, p.birthyear,
a.yearid, a.teamid, a.G_defense, b.H
FROM lahmansbaseballdb.appearances AS a
INNER JOIN lahmansbaseballdb.people AS p
ON p.playerid = a.playerid
INNER JOIN lahmansbaseballdb.batting AS b
ON p.playerid = b.playerid
WHERE b.yearid = 2017
AND b.H <> 0
ORDER BY p.playerid, a.yearid, a.teamid, a.G_defense, b.H;
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 19484 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 40 | lahmansbaseballdb.p.playerID,const | 1 | 90.00 | Using where |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 105627 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+---------+---------+------------------------------------+--------+----------+----------------------------------------------+
3 rows
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+------------------------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+------------------------------------+--------+----------+---------------------------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 105627 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | p | NULL | ref | NC_peopleID_birthYear | NC_peopleID_birthYear | 1023 | lahmansbaseballdb.a.playerID | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 40 | lahmansbaseballdb.p.playerID,const | 1 | 90.00 | Using where |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+------------------------------------+--------+----------+---------------------------------+
3 rows in set, 1 warning (0,01 sec)
+----+-------------+-------+------------+-------+-----------------------+---------------------------+---------+------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------+---------------------------+---------+------------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | a | NULL | index | NULL | NC_yearID_teamID_GDefense | 19 | NULL | 105627 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | NULL | ref | NC_peopleID_birthYear | NC_peopleID_birthYear | 1023 | lahmansbaseballdb.a.playerID | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | b | NULL | ref | PRIMARY | PRIMARY | 40 | lahmansbaseballdb.p.playerID,const | 1 | 90.00 | Using where |
+----+-------------+-------+------------+-------+-----------------------+---------------------------+---------+------------------------------------+--------+----------+----------------------------------------------+
3 rows in set, 1 warning (0,00 sec)
Hasil diatas setelah penambahan index pada tabel appearances tidak ada perbedaan, jadi tidak selamanya penambahan index mempercepat perfroma, jadi anda harus melakukannya dengan analisa yang mendalam dari setiap perubahan index, juga harus memperhatikan dampat pada query select yang lain.