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.

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://bptsi.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;

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

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

|-Contoh

#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