{ASK}: Bagaimana Caranya Supaya Proses Access ke MySQL lebih Cepat

Halo rekan-rekan semua, sebelum bertanya izinkan saya menjelaskan sedikit tentang database yang saya miliki.

(ini mengenai database suatu product) yang dalam MySQL saya tersimpan lebih dari 5juta baris, dan sudah saya berikan code Pagination dengan limit 6. Ketika saya akses ke pages yang mendekati halaman terakhir itu resource meningkat dan sangat lama sekali untuk menampilkan hasil mysql query tersebut.

Yang ingin saya tanyakan adalah, Adakah cara untuk mempercepat mysql query tersebut.
(total, Query took 7.4797 sec)
---------- Kondisi Server ----------

System hostname	-
Operating system	Debian Linux 6.0
Webmin version	1.610
Time on system	Wed Feb 20 09:21:59 2013
Kernel and CPU	Linux 2.6.32-5-686 on i686
Processor information	AMD Phenom(tm) 8450 Triple-Core Processor, 3 cores
System uptime	3 hours, 03 minutes
Running processes	120
CPU load averages	0.04 (1 min) 0.16 (5 mins) 0.11 (15 mins)
CPU usage	0% user, 0% kernel, 1% IO, 99% idle
Real memory	882.64 MB total, 211.79 MB used
Virtual memory	1.68 GB total, 2.38 MB used
Local disk space	291.74 GB total, 278.56 GB used

Sebelumnya saya ucapkan terimakasih atas bantuan dan jawabannya.
-------------

PHP:

        <?php
    if (empty($kw)) { $statement = "amazon"; $nama = "page";} else { $statement = "amazon WHERE cat LIKE '{$kw}' OR tags LIKE '{$kw}'"; $nama = $kw; }
    $query = "SELECT * FROM {$statement} LIMIT {$startpoint} , {$limit}";
    if ($result = mysql_query($query)) {
       while ($row = mysql_fetch_assoc($result)) {
    $title = limit_text($row['title'],35);
    $h_id = $row['id'];
    $local = $row['local'];
    $h_cat = $row['cat'];
    $h_tags = $row['tags'];
        ?>
Hasilnya disini
        <?php
            }
mysql_free_result($result);
}
?>
/**********/
Untuk page 1 s.d page 1000 an mungkin tidak begitu terasa, tapi di page hampir terakhir sekitar 600000, lama sekali


Gambar Structure DB

Spoiler

View attachment 27232

 

itu pagingnya udah dilimit di mysql query apa dari semua hasil fetch mysql diolah pake php boss?
kalau udah dilimit di mysql query msh lambat aku gak tau hrs gimana soalnya akh jg blm paham ama sql apalagi dg data segitu gede hehe

coba diganti2 pake fetch_row, fetch_array atau yg lain cari yg lebih cepet

atau barangkali code querynya dipost di sini, biar para mastah tau yg jadi penyebab lambatnya yg mana

 

Sudah saya Update boss pertanyaannya

 

5 juta row? aplikasi apaan tuh gan

ane belum expert banget, tapi coba kasih masukan yah.

yang mempengaruhi kecepatan query mysql bukan hanya teknik querynya tapi juga struktur table dan databasenya.

- untuk query, hindari penggunaan "select *", select bintang lebih makan resource karena si mysql bakal ngitung field2 yang ada di table tersebut sebelum nge run querynya
jadi biasakan lansung select `id`, `local`, `cat`, `tags` from `table_name`

- untuk struktur database, ada beberapa point
@ hindari penggunaan field type "text", "blob", dan sebagainya, gunakan "int", "varchar" selama itu masih memungkinkan.
@ gunakan field type UNSIGNED INT untuk primary key id, atau foreign key id, dan kasih index untuk kedua-duanya
@ gunakan NOT NULL untuk default value fieldnya

sementara itu dulu sih, untuk optimasi struktur database intinya kita memperkecil size table tersebut.

anyway, table dengan 5 juta row, bagaimana pun pasti akan lama querynya kalau di optimasi dari sisi koding saja, jika dirasa optimasi koding sudah maksimal tapi masih lambat, tambah aja ram servernya mas

 

waduh saya gak paham yg kaya beginian

 

coba skema tablenya paste disini,
dan juga sql query yg bermasalah..
nanti coba tak terawang

problem umum, biasanya ada indexing

dan kedua, di jumlah datanya.. 5jt dalam 1 tabel itu kebanyakan, pantes lemot
harus dipecah2 ke bbrp table.. namanya teknik sharding klo ga salah

 

Tapi anehnya boss, kalau order by asc / desc untuk page halaman awal kenceng-kenceng saja ini boss, baru di halaman mendekati akhir baru lemot sekali..

 

Belajar Teknik Sharding dimana boss ya? ada referensi URL yang bisa saya kunjungi?

 

google aja

btw field cat dan tags di index ga?
tipe tabelnya apa? klo memungkinkan coba diubah ke MyISAM row format=Fixed

 

ndak boss, tidak diindex, yang diindex cuman dua (Primary Column: id dan keyname id column code)

untuk Tipe table sudah MyISAM cuman row formatnya dynamic boss. Ini coba saya rubah ke fixed boxx
-----
Row Size di database ini kecil bos sebenarnya cuman: 185 B saja

 

pantes lemot,
cuma di index dua row tsb bos .. pasti beda rasanya

 

yang diindex harusnya apa aja boss?

 

yg diindex biasanya adalah field yg dipakai sbg filter di fungsi where

SELECT * FROM amazon where cat LIKE '%anu%' OR tags LIKE '%ee%'
yg diindex ya cat dan tags,

selain itu, coba explore lagi fungsi EXPLAIN
EXPLAIN gunanya utk mendebug performansi sebuah query
=> EXPLAIN SELECT * FROM amazon where cat LIKE '%anu%' OR tags LIKE '%ee%';
google aja utk lengkapnya ya hehe

 

Ooooo, begitu ya boss.... Trimakasih boss. Sangat membantu sekali....

ASK:
1. Apakah jumlah column itu mempengaruhi proses / resource juga boss?
2. Apakah kalau diringkas dengan json itu lebih ringan dibanding banyak column?
2. Apakah sebaiknya semuanya diindex boss?

Thanks you

 

1. pengaruh kyknya.. apalagi jumlah datanya banyak
2. pakai json ? ga tau eh..
nanti diringkas, malah susah filteringnya.. ya semua tergantung kebutuhan agan saja
3. ya jangan .. index itu kan utk mempercepat akses field2 yg sering diakses
dan dimasukkan ke memory biasanya ( parameter mysql key buffer ).

 

1. ngaruh donk.. pertama, lebih banyak kolom pasti lebih banyak data. misal simpan nama aja / nama & alamat.
lebih banyak data >> lebih banyak space >> file database makin besar.
file database makin besar, makin lambat.
mungkin kalau row nya masih sedikit blm ngaruh, kalo row nya udah banyak baru keliatan signifikan. Apalagi kalo banyak proses read / write ke database.
anggap aja coba cut & paste file 1kb sama file 1gb cepetan mana.

2. maksudnya misal kolom nama, alamat, umur, dijadiin 1 kolom dengan tipe text / varchar dan isinya berformat json?
lebih cepat / tidaknya tergantung penggunaan.
misal nama / alamat / umur itu sering dipakai dalam "where", tentu aja format kolom (plus indexing) lebih tepat.
tapi kalo nama / alamat / umur itu hanya utk ditampilkan saja & tidak pernah dipakai dalam where / group by / order by, format json menurut saya lebih oke (pilihan pribadi)
Plus jangan lupa string format json itu lambat dalam didecode. jadi kalo visitornya (proses view) banyak, lebih baik pake format serialize() aja.
karena serialize() itu lebih cepat dalam decode, tapi lebih lambat dalam encode.
jadi pilih sesuai kebutuhan aja

3. semua diindex, size index makin gede, bisa jadi pas proses insert malah lambat (karena ada proses add index lebih banyak), tapi pas proses select lebih cepat. tapi idealnya memang index hanya utk kolom yg sering masuk "WHERE" / "GROUP BY" aja. Jadi tidak wajib semua diindex. Kecuali emang dalam penggunaannya ada kemungkinan semua kolom masuk "WHERE"

 

Kalo dilihat dari query-nya, kemungkinan besar yang bikin lemot adalah query WHERE cat LIKE '{$kw}' OR tags LIKE '{$kw}'.
Karena bakal ngecek kemunculan substring $kw di kolom cat & tags di seluruh baris, pastinya butuh waktu lama.

Gimana kalo coba ditambahkan FullText index utk kedua kolom tersebut:
alter table amazon add FULLTEXT(cat,tags);


terus query WHERE cat LIKE '{$kw}' OR tags LIKE '{$kw}' diganti jadi :
WHERE MATCH(cat,tags) AGAINST ('{$kw}')

untuk string matching di mysql, rasanya fulltext index/searching cukup membantu.

sumber: h**p://viralpatel.net/blogs/full-text-search-using-mysql-full-text-search-capabilities/