select_type Menunjukkan tipe query, seperti PRIMARY, DERIVED, UNION, dll.
table Nama tabel atau hasil intermediate (seperti derived tables atau union).
type Menunjukkan tipe join atau akses data (ALL, ref, eq_ref, dll).
Berikut contoh query sesuai dengan nilai di kolom select_type dan type
1. PRIMARY + ALL
Query utama yang melakukan full table scan.
SELECT * FROM barang;
explain SELECT * FROM barang;
2. DERIVED + ALL
Derived table adalah subquery dalam klausa FROM. Full table scan digunakan untuk derived table.
SELECT * FROM (SELECT KODE_BARANG, NAMA_BARANG FROM barang) AS derived_table;
explain SELECT * FROM (SELECT KODE_BARANG, NAMA_BARANG FROM barang) AS derived_table;
3. DERIVED + ref
ref digunakan ketika indeks digunakan untuk mencari nilai tertentu, tetapi tidak unik.
SELECT p.TANGGAL_PASOK, p.JUMLAH_PASOK FROM pasok p JOIN barang b ON p.KODE_BARANG = b.KODE_BARANG
WHERE b.KODE_BARANG = 'ELK-03';
explain SELECT p.TANGGAL_PASOK, p.JUMLAH_PASOK FROM pasok p JOIN barang b ON p.KODE_BARANG = b.KODE_BARANG
WHERE b.KODE_BARANG = 'ELK-03';
4. DERIVED + eq_ref
eq_ref terjadi ketika join menggunakan kolom indexed yang bersifat unik (biasanya primary key atau unique key).
SELECT b.NAMA_BARANG, s.NAMA_SUPLIER
FROM barang b
JOIN pasok p ON b.KODE_BARANG = p.KODE_BARANG
JOIN suplier s ON p.KODE_SUPLIER = s.KODE_SUPLIER;
explain SELECT b.NAMA_BARANG, s.NAMA_SUPLIER
FROM barang b
JOIN pasok p ON b.KODE_BARANG = p.KODE_BARANG
JOIN suplier s ON p.KODE_SUPLIER = s.KODE_SUPLIER;
5. UNION + ref
UNION menggabungkan dua query, dan ref digunakan untuk mencari dengan indeks.
SELECT KODE_BARANG, JUMLAH_PASOK
FROM pasok
WHERE KODE_BARANG = 'ELK-03'
UNION
SELECT KODE_BARANG, JUMLAH_PASOK
FROM pasok
WHERE KODE_BARANG = 'ELK-04';
explain SELECT KODE_BARANG, JUMLAH_PASOK
FROM pasok
WHERE KODE_BARANG = 'ELK-03'
UNION
SELECT KODE_BARANG, JUMLAH_PASOK
FROM pasok
WHERE KODE_BARANG = 'ELK-04';
6. UNION RESULT + ALL
UNION RESULT menunjukkan hasil gabungan dari query UNION, dan ALL menunjukkan semua hasil.
SELECT *
FROM (
SELECT KODE_BARANG
FROM barang
UNION
SELECT KODE_SUPLIER
FROM suplier
) AS union_result;
explain SELECT *
FROM (
SELECT KODE_BARANG
FROM barang
UNION
SELECT KODE_SUPLIER
FROM suplier
) AS union_result;
7. joint
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
explain select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
8. sample add index for 7
Here's how you can create the non-clustered indexes:
CREATE INDEX idx_barang_kode_barang ON barang(KODE_BARANG);
CREATE INDEX idx_suplier_kode_suplier ON suplier(KODE_SUPLIER);
CREATE INDEX idx_pasok_kode_barang_kode_suplier ON pasok(KODE_BARANG, KODE_SUPLIER);
After creating these indexes, your query should benefit from improved performance when joining the tables.
If you want to check the index structure before adding the new ones, you can run the following command for each table:
SHOW INDEXES FROM barang;
SHOW INDEXES FROM suplier;
SHOW INDEXES FROM pasok;
jika drop
To drop the index on barang(KODE_BARANG):
DROP INDEX idx_barang_kode_barang ON barang;
DROP INDEX idx_suplier_kode_suplier ON suplier;
DROP INDEX idx_pasok_kode_barang_kode_suplier ON pasok;
Catatan Penting
Pastikan indeks sudah diatur dengan benar pada tabel Anda untuk mengoptimalkan query.
Gunakan EXPLAIN untuk menganalisis query dan melihat bagaimana query optimizer memprosesnya.
9. check Index
SELECT table_catalog,table_schema,
table_name,table_type,table_rows,create_time,update_time,
ROUND(data_length / 1024 / 1024, 2) AS data_size_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_size_mb
FROM
information_schema.tables
WHERE
table_schema = 'teguhth'
ORDER BY index_size_mb DESC;
10. check list index
select index_schema,
index_name,
group_concat(column_name order by seq_in_index) as index_columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique,
table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
AND table_schema='teguhth'
group by index_schema,
index_name,
index_type,
non_unique,
table_name
order by index_schema,
index_name;
select index_schema,
index_name,
group_concat(column_name order by seq_in_index) as index_columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique,
table_name
from information_schema.statistics
where table_schema in ('teguhth')
group by index_schema,
index_name,
index_type,
non_unique,
table_name
order by index_schema,
index_name;
11. list index structure
SHOW INDEXES FROM barang;
SHOW INDEXES FROM suplier;
SHOW INDEXES FROM pasok;
SELECT
TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
INDEX_NAME AS `Index`,
COLUMN_NAME AS `Column`,
NON_UNIQUE AS `Is_Non_Unique`,
SEQ_IN_INDEX AS `Seq_in_Index`,
INDEX_TYPE AS `Index_Type`
FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
TABLE_SCHEMA = 'teguhth' and TABLE_NAME = 'barang';
12. check size Index
SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND index_name != 'PRIMARY'
ORDER BY size_in_mb DESC;
SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND TABLE_NAME='barang'
AND database_name ='teguhth'
ORDER BY size_in_mb DESC;
13. check usage index
select
database_name,
table_name,
index_name,
round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB,
round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis
where stat_name='size'
and database_name = 'teguhth';
No comments:
Post a Comment