My SQL Query Optimization (part 2)

menyambung query optimization yang pernah saya tulis sebelumnya di artikel : http://hanjar.net/my-sql-query-optimization/
yang saya lakukan adalah mengubah struktur tabel,
a. field GBSC sebelumnya varchar 100 menjadi varchar 30;
b. index non-unique saya remove, index unique saya biarkan :
sebelum dihapus

setelah dihapus

hasil sebelum di remove index dan mengurangi varchar masih 100
select * from 2g_hourly where cellci = 35343 and gbsc = 'bsc_parakansaat_02' limit 10;
Result : 5,110 sec

hasil sebelum di remove index dan mengurangi varchar masih 100
select * from 2g_hourly where cellci = 35343 and gbsc = 'bsc_parakansaat_02' limit 10;
Result : 0,843  sec

ada improvement signifikan disana tetapi masih belum tahu apakah improvementnya dikarenakan oleh removing index atau karena mengurangi tipe field dari varchar 100 menjadi varchar 30;

percobaan dilanjutkan menggunakan tabel dengan gbsc varchar 100 dan index non unique diremove :

select * from 2g_hourly where cellci = 35343 and gbsc = 'bsc_parakansaat_02' limit 10;

hasilnya 1,312 sec.

kesimpulan :

  1. terlalu banyak index dan gbsc varchar (100) -> slow query 5,110 sec.
  2. remove index improve query menjadi 1,312 sec.
  3. remove index, dan mengubah gbsc dari varchar(100) menjadi varchar (25) dibutuhkan 0,843 sec.

mysql batch command

jika ingin melakukan eksekusi beberapa query dalam satu batch :

misalkan kita siapkan file batch query.txt dengan isi

select colA from TableA;
select colB from TableB;
select colC from TableC;

setelah file tersebut disimpan, bisa dieksekusi menggunakan command berikut :
mysql -h$host -u$user -p$password db_dbname < query.txt

My SQL Query Optimization

Background :

  • Table size : 29,9 GB
  • Engine : MyISAM
  • Table Structure :
    Field Type Null Key Default Extra
    Date date NO PRI
    Time time NO PRI
    GBSC varchar(100) NO MUL
    CellCI int(10) NO PRI
    CellName varchar(100) NO
    CellIndex varchar(100) NO
    CellLAC int(10) NO PRI 0
    Integrity double NO 0
  • Query :
    select * from 2g_hourly where cellci = 35343 and gbsc = 'bsc_parakansaat_02' limit 10;
    Result
    : 5,110 sec

Ada yang menarik ketika menjalankan query tersebut, jika hanya menggunakan 1 syarat cellci tanpa gbsc  :
select * from 2g_hourly where cellci = 35343 limit 10;

hanya dibutuhkan 0,688 sec

jika langsung menggunakan query menggunakan syarat cellname tanpa gbsc

select * from 2g_hourly where cellname = 'PRKN_SAAT3' limit 10;

dibutuhkan 1,313 sec.

Dimulailah percobaan :
1. Syarat Where dibalik syarat gbsc terlebih dahulu lalu syarat cellci   :
select * from 2g_hourly where gbsc = 'bsc_parakansaat_02' and cellci = 35343 limit 10;
Result 4,078 sec.

2. Subquery query cellci   terlebih dahulu lalu diquery gbsc :
select * from (select * from 2g_hourly where cellci = 35343) as a where a.gbsc = 'bsc_parakansaat_02' limit 10;
Result 4,078 sec.

3. Subquery dibalik dari sequence sebelumnya query gbsc terlebih dahulu lalu diquery cellci   :
select * from (select * from 2g_hourly where gbsc = 'bsc_parakansaat_02' ) as a where a.cellci = 35343 limit 10;

Result 4,187 sec.

4. Ganti syarat =  menggunakan in dimana basis awal diubah menjadi :
select * from 2g_hourly where cellci in (35343) limit 10; limit 10; 

Result 0,688 sec.

lalu dilanjutkan dengan syarat gbsc :
select * from 2g_hourly where cellci in (35343) and gbsc in ('BSC_PARAKANSAAT_02') limit 10;

hasilnya lebih dari 1 menit belum selesai query-nya, saya hentikan.

kesimpulan sementara :

  1. Syarat menggunakan (cellci) integer jauh lebih cepat dari pada menggunakan (cellname) string.
  2. Menggunakan lebih dari 1 syarat lebih lambat daripada menggunakan 1 syarat.
  3. Urutan syarat mempengaruhi kecepatan pencarian.
  4. syarat menggunakan “in” untuk tipe integer tidak ada perbedaan dengan “=“.
  5. syarat menggunakan “in” untuk tipe string lebih lambat dibandingkan menggunakan “=“.

to be continue… mengubah tabel struktur