Categories
Dokumentasi Tutorial, Manual, Tips dan Trik

Tips Query MySQL

MySQL adalah sebuah perangkat lunak sistem manajemen basis data SQL atau DBMS yang multialur, multipengguna dan banyak digunakan di seluruh dunia. Query adalah perintah yang digunakan untuk memanipulasi atau mendapatkan data dari basis data.

Daftar Isi

Tips Query MySQL

Programmer memiliki kebebasan dalam menarik data dari basisdata menggunakan Query. Terkadang ada idealisme hasil query yang diiinginkan tetapi tidak tahu bagaimana caranya karena tidak ada fasilitas bawaan yang disediakan, seperti crosstab, query cepat dan sebagainya.

— Tips Query MySQL
https://pdsi.unisayogya.ac.id/tips-query-mysql/ 2022-01-12 14:28:26

Tabel Sumber

CREATE TABLE `presensi_nama` (
  `idpresensinama` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idpresensinama`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE `presensi` (
  `idpresensi` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idpresensinama` int(10) unsigned DEFAULT NULL,
  `tanggal` date DEFAULT NULL,
  `hadir` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`idpresensi`),
  KEY `fk_idpresensinama_idx` (`idpresensinama`),
  KEY `idx_tanggal` (`tanggal`),
  CONSTRAINT `fk_idpresensinama` FOREIGN KEY (`idpresensinama`) REFERENCES `presensi_nama` (`idpresensinama`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
idpresensinamanama
1John Doe
2Fulan
tabel presensi_nama
idpresensiidpresensinamatanggalhadir
112022-01-021
222022-01-020
312022-01-031
422022-01-031
512022-01-041
622022-01-041
712022-01-051
tabel presensi

MySQL

Crosstab Statis

|-Cara

menggunakan kondisi di dalam aggregate

|-Contoh

#alternatif 1
select nama, sum(if(hadir=1,1,0)) as `hadir`, sum(if(hadir=0,1,0)) as `tidak hadir` from presensi p join presensi_nama pn on pn.idpresensinama=p.idpresensinama group by p.idpresensinama;
#alternatif 2
select nama, sum(hadir=1) as `hadir`, sum(hadir=0) as `tidak hadir` from presensi p join presensi_nama pn on pn.idpresensinama=p.idpresensinama group by p.idpresensinama;

|-Hasil

namahadirtidak hadir
John Doe40
Fulan21

Crosstab Dinamis

|-Cara

  1. Buat query crosstab statis; tandai query untuk crosstab
  2. Tandai field yang digunakan di dalam crosstab dan berada di tabel apa; kemudian diubah ke dalam bentuk group_concat
pemecahan query
Pemecahan Query

|-Contoh

Step Pemecahan Query
#hasil akhir
select @crosstab := concat("select nama,", group_concat(distinct concat("sum(hadir=", hadir, ") as `hadir", hadir,"`") separator ","), " from presensi p join presensi_nama pn on pn.idpresensinama=p.idpresensinama group by p.idpresensinama") from presensi;
prepare rekap_presensi from @crosstab;
execute rekap_presensi;
deallocate prepare rekap_presensi;

Slow Query

|-Mitigasi

  1. Menggunakan klausa explain, atau
  2. Membaca log slow query, atau
  3. Monitoring koneksi

|-Kemungkinan Penyebab

  1. Penggunaan klausa where atau join tanpa melibatkan index [solusi: tambahkan index/unique/foreign key], atau
  2. Penggunaan klausa where yang tidak tepat pada sub query/aggregate [solusi: gunakan klausa where seawal mungkin (di dalam sub query), misalnya select * from (select * from presensi where year(tanggal)=2022)], atau
  3. Penggunaan klausa in dengan sub query (misalnya select * from presensi where idpresensinama in (select idpresensinama from presensinama where nama like '%fu%')) [solusi: ubah ke dalam bentuk join, misalnya select * from presensi p join presensinama pn on pn.idpresensinama=p.idpresensinama where nama like '%fu%')], atau
  4. Menggunakan view yang berjenis undefine/temporary table [solusi: menghindari view yang berjenis undefine/temporary table], atau
  5. Penggunaan aggregate pada tabel yang besar [solusi: menggunakan fasilitas partition]

|-Contoh Mitigasi

  1. Contoh penggunaan klausa explain
    explain select nama, sum(hadir=1) as hadir, sum(hadir=0) as tidak hadir from presensi p join presensi_nama pn on pn.idpresensinama=p.idpresensinama group by p.idpresensinama;
    perhatikan informasi pada possible key dan extra; sebisa mungkin possible key tidak berisi null
  2. mysqldumpslow /var/log/mysql/mysql-slow.log
  3. Contoh monitoring koneksi untuk menemukan slow query dan solusinya
    https://pdsi.unisayogya.ac.id/setting-moodle-untuk-administrator-atau-programmer/4#slow-query-moodle

By basit

Biro Pengembangan Teknologi Dan Sistem Informasi

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.