Cara menggunakan mysql max rows performance

Waktu Baca : 8 menit

Salah satu script saya sering error, tulisannya :

Database error: Invalid SQL: select * from leads quick where lnextduedate=’0000-00-00′ and lnextdue=” and lstatus=’A’MySQL Error: 2008 (MySQL client ran out of memory)Session halted.

Saya ganti harddisknya menggunakan SSD dan memori RAM ditambahpun tidak ada pengaruhnya. Wah gmn cara ngatasinnya ya???

Optimasi dapat dilakukan dengan berbagai cara, dengan memahami tuning performance pada database dan best practice dari berbagai sumber, Anda dapat memiliki fundamental yang kuat dalam mengoptimalkan kinerja database.

Beberapa teknik dan metoda mungkin memerlukan perlakuan khusus yang berbeda, tergantung pada database yang Anda gunakan. Sebagai contoh, peningkatan kinerja bisa dilakukan dari sisi administrasi database seperti konﬁgurasi ﬁle dan peng-updatean service atau security pack, yang tentunya masing-masing database memiliki keunikan dan teknik tersendiri.

Terdapat seperangkat metode dan teknik yang umum diterapkan saat Anda bekerja dengan RDBMS (Relational Database Management System), mungkin tidak semuanya dapat Anda implementasikan karena sangat tergantung pada lingkungan aplikasi masing-masing, tetapi setidaknya Anda dapat meng-gunakannya sebagai panduan dan referensi untuk membentuk sistem yang terbaik sesuai dengan kondisi yang dihadapi.

Optimasi melalui perintah SQL juga memegang peranan yang tidak kalah penting. Inti dari SQL itu sendiri adalah perintah untuk melakukan pengambilan (retrieval), penambahan (insertion), modiﬁkasi (updating), dan penghapusan (deletion) data, disertai dengan fungsi-fungsi pendukung administrasi dan managemen database.

SQL sendiri merupakan sebuah bahasa atau pemrograman standar untuk RDBMS. Walaupun disebut bahasa, mungkin sedikit janggal saat kita menyebut bahasa pemrograman SQL, lebih familiar jika yang terdengar adalah pemrograman C, Visual Basic, Java, Delphi, dan seterusnya.

Bahasa-bahasa yang disebut belakangan termasuk dalam pemrograman imperative, mudahnya adalah bahasa yang berbentuk instruksi-instruksi inti. Sedangkan, SQL termasuk dalam pemrograman declarative, yang lebih berbentuk kalimat atau pernyataan.

Dalam pengembangannya, SQL terbagi-bagi lagi dalam berbagai extension sehingga melahirkan berbagai sebutan seperti SQL/PSM (Persistent Stored Modules) yang merupakan standar ANSI/ISO, T-SQL (Transact-SQL) dari Microsoft dan SyBase, PL/SQL (PL merupakan singkatan dari Procedural Language) yang digunakan oleh Oracle, yang kemudian dikembangkan lagi menjadi PL/pgSQL yang digunakan PostgreSQL.

Cukup membingungkan, bukan? Untungnya konsep dan elemen-elemen dasar dalam SQL seperti statement, query, expression, ataupun clause tetap berlaku umum pada setiap SQL extension.

Kita cukupkan pembahasan teori sampai di sini, berikut adalah beberapa optimasi sederhana yang dapat Anda lakukan, untuk setidaknya memperbaiki atau mencegah permasalahan, dan meningkatkan performa RDBMS Anda.

Berikut beberapa cara optimasi query SQL :

1. Index

Mengindeks kolom dalam tabel adalah cara yang biasa dilakukan untuk mengoptimalkan hasil pencarian query SQL. Membuat indeks dalam sql tak ubahnya seperti membuat daftar indeks pada buku. Bayangkan ketika anda ingin mencari sebuah section pada buku mengenai suatu hal dan harus mencari setiap halaman dari awal sampai akhir, dengan indeks anda cukup mencari urutan indeks kata tersebut dan langsung membuka halaman rujukan yang ada pada indeks. Penggunaan indeks pada eksekusi query SQL akan benar-benar terasa ketika tabel dan basis data yang kita punya sudah cukup besar.

Apakah index scan selalu lebih cepat dibandingkan dengan table scan? Ternyata tidak juga! Table scan bisa jadi bekerja lebih cepat saat mengakses record dalam jumlah relatif kecil, ataupun pada saat aplikasi memang memerlukan pembacaan table secara keseluruhan. Sebaliknya dalam mengakses record yang besar pada ﬁeld tertentu, index scan dapat mengurangi operasi pembacaan I/O sehingga tidak jarang menghasilkan kinerja yang lebih cepat.

Sebagai patokan, Anda dapat menentukan index pada ﬁeld yang sering digunakan, misalnya ﬁeld yang sering diakses oleh klausa WHERE, JOIN, ORDER BY, GROUP BY.

2. Symbol Operator

Penggunaan operator simbol seperti <, >, =, !=, ><, dll sangat membantu dalam menghasilkan kecepatan pencarian dari query. Cara kerja database management system (DBMS) dalam menerapkan query dengan kondisi “>” dibandingkan dengan “=>” akan sedikit berbeda, apabila kita menggunakan “=>” maka DBMS akan menerapkan dua kriteria pada setiap hasil, tidak langsung mencari pada kondisi langsung seperti apabila kita menggunakan “>”.

3. Wildcard (kartu liar)

Wildcard yang digambarkan dengan simbol “%” yang kita tuliskan pada kondisi/kriteria query akan sangat berpengaruh pada lamanya proses pencarian. Wildcard sendiri dapat kita bagi menjadi tiga bagian penggunaan, yaitu wildcard penuh (contoh ‘%kata%’), postfix wildcard (contoh ‘kata%’), dan prefix wildcard (contoh ‘%kata’). Sebisa mungkin hindari penggunaan wildcard penuh karena untuk ukuran tabel dengan baris yang banyak, dan pencarian kolom dengan panjang kolom yang besar akan benar-benar menyiksa basis data dalam melakukan pencarian.

4. Operator Negatif

Penggunaan operator negatif (seperti NOT, NOT LIKE, NOT IN, NOT EXIST, != dll) akan lebih memakan waktu lama dibandingkan operator positif. Operator = merupakan operator paling memudahkan database dalam melakukan pencarian, terutama ketika database sudah terindeks maka pencarian exact match ini akan dihasilkan dengan cepat.

5. COUNT vs EXIST

Seringkali dalam pembuatan sebuah loop dan logika dalam aplikasi- kita ingin membuat penguji sederhana dengan pembuatan perintah yang dilaksanakan hanya jika sebuah nilai ada dalam tabel (pada kolom tertentu dalam tabel) – kita menggunakan COUNT sebagai kriteria. Dalam artian ketika COUNT menghasilkan angka > 0 maka loop atau logika akan dijalankan.

SELECT KOLOM FROM TABEL WHERE COUNT > 0

Penggunaan COUNT akan melakukan pencarian dari awal hingga akhir baris dari tabel. Sebagai alternatif yang lebih baik anda dapat menggunakan EXIST, kriteria ini akan menghentikan pencarian ketika kriteria/kondisi sudah ditemukan.

6. Wildcard vs Substr

Penggunaan substr() dalam query akan membuat DBMS berusaha memotong seluruh kolom dalam sebuah tabel sesuai kondisi substr(). Sebagai alternatif yang lebih baik dapat digunakan postfix wildcard untuk membuat pencarian menjadi lebih cepat.

7. Index Unique Column

Hal ini masih berhubungan dengan indexing pada bahasan pertama. Index bertipe unik (unique) dalam beberapa DBMS akan memberikan kecepatan pencarian tersendiri, terutama index yang paling sering dilakukan pencarian.

8. Operator Max dan Min

Max dan Min adalah operator yang sebisa mungkin tidak terlalu sering digunakan karena pencarian yang digunakan dilakukan secara menyeluruh dari awal sampai akhir tabel. Penggunaan index akan sangat membantu jika nilai maksimum/minimum yang dijadikan sebagai kriteria adalah indeks. Tapi pada umumnya tipe kolom yang dijadikan kriteria adalah int, dec, dan date/time.

9. Tipe Data

Gunakan tipe data yang paling kecil yang dapat digunakan dengan turut memperhatikan kebutuhan dimasa yang akan datang. Penggunaan tinyint dan int akan memberikan hasil yang berbeda, begitu juga dengan hasil pencarian.

10. Primary Index

Primary Column yang digunakan untuk indeks harus dibuat sependek mungkin, dengan begitu identifikasi dari baris-baris yang ada akan lebih efisien bagi DBMS.

11. String Indexing

Pengindeks-an seluruh string sebenarnya tidak perlu dilakukan karena akan lebih baik jika kita menggunakan prefix atau postfix dari string akan menghasilkan daftar isi yang lebih ringkas dan efisien. Ingat, semakin pendek indeks maka pencarian akan menjadi lebih cepat dan lebih hemat sumber daya.

12. Batasi Hasil Query

Hasil query yang menampilkan seluruh tabel memang tidak terlalu masalah jika memang kita membutuhkan hasil penuh. Namun ketika berbicara aplikasi yang akan dilihat oleh pengguna (bukan pengolahan data lebih lanjut) maka membatasi hasil query akan lebih baik. Pagination dengan menggunakan LIMIT sebesar 10, 30, atau 50 dirasa sudah cukup untuk menampilkan aplikasi yang akan dibaca oleh pengguna.

13. Gunakan Nilai Otomatis (Default Value)

Penggunaan nilai otomatis pada beberapa DBMS yang menyediakan fitur ini sangat bermanfaat terutama dalam hal melakukan insertpada basis data. Nilai otomatis akan mempercepat kerja DBMS dalam Insert data ketika tidak ada nilai yang dikirim dari query yang dieksekusi.

14. IN SUBQUERY

Penggunaan IN umum digunakan ketika kriteria yang kita gunakan berupa array. Penggunaan subquery sering digunakan dalam membentuk kriteria dinamis yang akan digunakan dalam kriteria IN. Namun hal ini akan lebih berat karena DBMS akan menggunakan kriteria induk terlebih dahulu, baru kemudian menggunakan kriteria subquery. Dalam hal ini DBMS bekerja 3 tahap, dimana dengan kriteria IN yang konstan hanya akan menjalankan 1 tahap pencarian saja.

Sebagai alternatif anda dapat membuat array dengan mendeclare terlebih dahulu subquery ke dalam sebuah variabel, dengan begitu variabel ini dapat digunakan berkali-kali dalam query yang lain. Akan lebih baik jika kita melibatkan sumber daya yang dimiliki pengguna dengan membuat session.

15. Menggunakan UNION dibandingkan OR

Penggunaan OR sebagai kriteria berganda adalah jamak dilakukan. Namun ada alternatif lain yang dapat dibuat yaitu dengan menggunakan UNION apabila kolom yang ingin kita cari adalah sama. Namun tentunya penggunaan UNION ini memiliki keterbatasan. Perbedaan kecepatan untuk kriteria OR, UNION, dan IN sebenarnya dapat diperdebatkan mengingat ketiga kondisi kriteria di atas memerlukan strategi algoritma koding yang berbeda dari tiap programmer.

16. Hindari SELECT *

Select mungkin merupakan keyword yang paling sering digunakan, karena itu optimasi pada perintah SELECT sangat mungkin dapat memperbaiki kinerja aplikasi secara keseluruhan.SELECT * digunakan untuk melakukan query semua ﬁeld yang terdapat pada sebuah table, tetapi jika Anda hanya ingin memproses ﬁeld tertentu, maka sebaiknya Anda menuliskan ﬁeld yang ingin diakses saja, sehingga query Anda menjadi SELECT ﬁeld1, ﬁeld2, ﬁeld3 dan seterusnya (jangan pedulikan kode program yang menjadi lebih panjang!). Hal ini akan mengurangi beban lalu lintas jaringan dan lock pada table, terutama jika table tersebut memiliki banyak ﬁeld dan berukuran besar.

17. Batasi ORDER BY

Penggunaan ORDER BY yang berfungsi untuk mengurutkan data, ternyata memiliki konsekuensi menambah beban query, karena akan menambah satu proses lagi, yaitu proses sort.Karena itu gunakan ORDER BY hanya jika benar-benar dibutuhkan oleh aplikasi Anda.Atau jika dimungkinkan, Anda dapat melakukan pengurutan pada sisi client dan tidak pada sisi server. Misalnya dengan menampung data terlebih dahulu pada komponen grid dan melakukan sortir pada grid tersebut sesuai kebutuhan pengguna.

18. Subquery Atau JOIN

Adakalanya sebuah instruksi dapat dituliskan dalam bentuk subquery atau perintah JOIN, disarankan Anda memprioritaskan penggunaan JOIN karena dalam kasus yang umum akan menghasilkan performa yang lebih cepat.Walaupun demikian, mengolah query merupakan suatu seni, selalu ada kemungkinan ternyata subquery bekerja lebih cepat dibandingkan JOIN, misalnya dalam kondisi penggunaanJOIN yang terlalu banyak, ataupun logika query yang belum optimal.

19. Gunakan WHERE dalam SELECT

“Di mana ada gula di sana ada semut”. Untuk programer database, pepatah itu perlu dimodiﬁ kasi menjadi “di mana ada SELECT di sana ada WHERE”, untuk mengingatkan pentingnya klausa WHERE sebagai kondisi untuk menyaring record sehingga meminimalkan beban jaringan.Saat sebuah table dengan jumlah data yang sangat besar diproses, juga terjadi proses lock terhadap table tersebut sehingga menyulitkan pengaksesan table yang bersangkutan oleh pengguna yang lain.Bahkan jika Anda bermaksud memanggil seluruh record, tetap menggunakan WHERE merupakan kebiasaan yang baik.Jika Anda telah menggunakan WHERE pada awal query, maka kapanpun Anda ingin menambahkan kondisi tertentu, Anda tinggal menyambung query tersebut dengan klausa AND diikuti kondisi yang diinginkan.
Tapi bagaimana menggunakan WHERE jika benar-benar tidak ada kondisi apapun? Anda dapat menuliskan suatu kondisi yang pasti bernilai true, misalnya SELECT …. WHERE 1=1. Bahkan tools open source phpMyAdmin yang berfungsi untuk mena ngani database MySQL selalu menyertakan default klausa WHERE 1 pada perintah SELECT, di mana angka 1 pada MySQL berarti nilai true.

20. Kecepatan Akses Operator

WHERE 1=1 dan WHERE 0 <> 1 sama-sama merupakan kondisi yang menghasilkan nilai true. Tetapi, dalam hal ini lebih baik Anda menggunakan WHERE 1=1 daripada WHERE 0 <> 1. Hal ini dikarenakan operator = diproses lebih cepat dibandingkan dengan operator <>.Dari sisi kinerja, urutan operator yang diproses paling cepat adalah:1. =2. >, >=, <. <=3. LIKE4. <>Tidak dalam setiap kondisi operator dapat disubtitusikan seperti contoh sederhana di atas, tetapi prioritaskanlah penggunaan operator yang tercepat.

21. Membatasi Jumlah Record

Bayangkan Anda menampilkan isi sebuah table dengan menggunakan SELECT, dan ternyata table tersebut memiliki jutaan record yang sangat tidak diharapkan untuk tampil seluruhnya.Skenario yang lebih buruk masih dapat terjadi, yaitu query tersebut diakses oleh ratusan pengguna lain dalam waktu bersamaan!Untuk itu, Anda perlu membatasi jumlah record yang berpotensi mengembalikan record dalam jumlah besar (kecuali memang benar-benar dibutuhkan), pada SQL Server, Anda dapat menggunakan operator TOP di dalam perintah SELECT.Contohnya SELECT TOP 100 nama… akan menampilkan 100 record teratas ﬁeld nama.Jika menggunakan MySQL, Anda dapat menggunakan LIMIT untuk keperluan yang sama.

22. Batasi Penggunaan Function

Gunakan fungsi-fungsi yang disediakan SQL seperlunya saja.Sebagai contoh, jika Anda menemukan query sebagai berikut: SELECT nama FROM tbl_teman WHERE ucase(nama) = ‘ABC’, nampak query tersebut ingin mencari record yang memiliki data berisi “abc”, fungsi ucase digunakan untuk mengubah isi ﬁeld nama menjadi huruf besar dan dibandingkan dengan konstanta “ABC” untuk meyakinkan bahwa semua data “abc” akan tampil, walaupun dituliskan dengan huruf kecil, besar, ataupun kombinasinya.Tetapi, cobalah mengganti query tersebut menjadi SELECT nama FROM tbl_teman WHERE nama = ‘ABC’, perhatikan query ini tidak menggunakan function ucase. Apakah menghasilkan result yang sama dengan query pertama? Jika pengaturan database Anda tidak case-sensitive (dan umumnya secara default memang tidak case-sensitive), maka hasil kedua query tersebut adalah sama. Artinya, dalam kasus ini Anda sebenarnya tidak perlu menggunakan function ucase!

23. Baca dari Kiri ke Kanan

Query yang Anda tulis akan diproses dari kiri ke kanan, misalkan terdapat query WHERE kondisi1 AND kondisi2 AND kondisi3, maka kondisi1 akan terlebih dahulu dievaluasi, lalu kemudian kondisi2, kondisi3, dan seterusnya. Tentunya dengan asumsi tidak ada kondisi yang diprioritaskan/dikelompokkan dengan menggunakan tanda kurung. Logika operator AND akan langsung menghasilkan nilai false saat ditemukan salah satu kondisi false, maka letakkan kondisi yang paling mungkin memiliki nilai false pada posisi paling kiri. Hal ini dimaksudkan agar SQL tidak perlu lagi mengevaluasi kondisi berikutnya saat menemukan salah satu kondisi telah bernilai false.

Jika Anda bingung memilih kondisi mana yang layak menempati posisi terkiri karena kemungkinan falsenya sama atau tidak bisa diprediksi, pilih kondisi yang lebih sederhana untuk diproses.