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

🪄 Useful git spells

Navigating the world of Git can be akin to mastering an arcane art. For the savvy developer, knowing the right incantations can mean the difference between seamless collaboration and catastrophic code conflicts. Here's a compendium of Git commands that are essential for those critical moments. Handle them with care, for they wield great power. Reverting to a Specific Commit (Non-Shared Branches Only) When you need to undo changes and return to a known good state: `git reset --hard <commit-hash>` - This will reset your branch to the specified commit. `git push -f origin` - Force push the changes to overwrite the remote branch. Merging Branches Like a Pro To incorporate changes from one branch into another, follow these steps: `git checkout <branch-to-merge-to>` - Switch to the branch you want to update. `git fetch origin` - Fetch the latest changes from the remote. `git pull` - Pull the latest changes into your local branch. `git status` - Check the status of your branch...
Post a Comment
Read more