Skip to main content

SQL Tuning


Pengenalan

Page korang load slow? Rekod berjuta-juta?  Client bising kena tunggu lebih 10 saat untuk page load? 

Say no more.

Perkongsian ilmu kali ni pasal query tuning. Bukan ecu kenderaan je boleh tune untuk bagi laju, database & query pun boleh. Even 1 saat tunggu pun dah boleh masuk kategori slow.

Jadi, jom belajar cara lajukan query.

Optimize select

  • Jangan guna select *
    • Aku berani bet, kau tak guna pun semua rekod. Max pun lah, maybe 90% field guna. 10% off masih berbaloi untuk buat select panjang2.
  • Pastikan tak select benda sama dua kali

 

Optimize penggunaan functions

Certain functions create temporary table untuk process data sebelum return value. High cost, better buat cara lain.
  • DISTINCT ➡️ lagi laju guna GROUP BY
  • GROUP_CONCAT ➡️ boleh buat satu query lain yg simple, then process, merge both array of object
  • ORDER BY ➡️ sort dkt frontend shj, backend tak perlu

Optimize subquery

Masa join table, dia akan select all field. Better select terus field apa yg kita nak masa join tu.
  • JOIN (SELECT f_name, m_name FROM parents) p ON s.parent_id = p.id

Kecilkan scope data masa join. Tambah where condition terus.
  • JOIN (SELECT sessions, program_id FROM programs_students WHERE sessions = 2026 AND org_id = 1470) ps ON s.id = ps.student_id

Optimize where condition

Pass variable yang sama type dalam where. Kalau tak, cost naik sikit sebab nak kena convert.
  • WHERE id = ‘123’  ➡️ WHERE id = 123

Specific value lebih laju dari functions. Guna = lagi laju dari guna LIKE, YEAR, DATE etc. 
  • YEAR(attend_date) = 2026 ❌
  • attend_date >= ’2026-01-01’ and  attend_date <= ‘2026-12-31’ ✅

Optimize indexing

Tambah index dkt column yang selalu guna WHERE. 
  • Contoh yang biasa takde index sekarang: doc_no, deleted_at.

Kalau ada beberapa column yg memang guna in pair, bolah buat satu composite index. Satu index, dua column.

  • Index tak boleh banyak sangat, nanti insert & update pula jadi berat.

Conclusion

Boleh cuba praktikkan ilmu yang dah dipelajari, semoga database laju berdesup & semoga client tak bising sistem slow. Amin.
Labels:

Comments

Post a Comment

Popular posts from this blog

Setup existing IONIC project in local

Setup existing IONIC project in local  Steps: clone git repo install ionic -  npm install -g @ionic/cli masuk folder repo project install npm dependencies -  npm install run ionic project -  ionic serve buka android studio / xcode -  ionic cap open $var  - $var = ‘android’ atau ‘ios’. ada dua je option, replace $var dgn dua option tu sync changes vscode & dkt android studio / xcode -  ionic cap sync good luck Common issues: dependency conflict check https://www.npmpeer.dev/ utk tengok version yg compatible try naikkan/turunkan version dependency yg keluar dkt error. tembak je sampai hilang error g radle issue try upgrade gradle. kalau tak boleh, try remove folder android & build semula e rror cocoapod make sure install xcode make sure install cocoapod error java  home not found utk mac, buka ~/.zshrc & masukkan chang es dkt VSC tak masuk android studio / xcode try ionic cap sync try quit & buka semula cordo va.variables.gradle no...
Post a Comment
Read more

🗑️ Clear storage Mac OS

  🗑️ Clear storage Mac OS 1: Clear system cache: Go to Finder > Go > Go to Folder, then type in "~/Library/Caches" and hit enter. Select all the folders inside the Caches folder and delete them. 2: Clear system logs: Go to Finder > Go > Go to Folder, then type in "/var/log" and hit enter. Select all the files inside the Log folder and delete them. 3: Remove unused language files: Go to Finder > Go > Go to Folder, then type in "/Library/Languages" and hit enter. Delete all the language folders you don't need. 4: Uninstall unused apps: Go to the Applications folder and delete the apps you don't use. 5: Clean up system files: Use a system cleaning tool like CleanMyMac X to scan and remove unnecessary system files. 6: If you have npm installed, clear the caches once in a while with ‘sudo npm cache clean --force’ 7: If you have ionic projects, open the ‘.angular’ folder and delete the ‘cache’ folder inside it.
Post a Comment
Read more

Other useful things I sometime use but always forgot

  🐘 Useful PHP functions One line year array // will get the past 3 year including current year. eg: 2021,2022,2023 for ($i=-2; $i <= 0 ; $i++) $data['year'][date('Y')+$i] = date('Y')+$i; Unset attribute in array of array object $body_list[$count] = array(             'attr' => array(                 '1' => 'one',                 '2' => 'two',             ),             'data' => (object) array(                 '3' => 'three',                 '4' => 'four',             )         ); unset($body_list[$count]['data’]->{‘3’}); // will remove attribute ‘three’ Merge/combine array with duplicate keys while preserving the values // use this inside loops where + won...
Post a Comment
Read more