Skip to main content

SQL Tuning


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

Vulnerabilities in Dependencies

Vulnerabilities in Dependencies - A Lesson from 4Chan Case Study: The 4Chan Security Breach The Neglect 4Chan had not updated its project dependencies since 2009. The Vulnerability One specific dependency was publicly known to have multiple critical vulnerabilities: Ghostscript.  Ghostscript Vulnerability Reports (Snyk) Attack Vectors: Local File Inclusion (LFI): The system processed PDF files without verifying if the uploaded file was actually a PDF. Remote Code Execution (RCE): After a successful injection, attackers could run malicious scripts directly on the server. The Wake-Up Call This event serves as a modern reminder of the critical importance of keeping dependencies up to date. Dependencies Audit & Status These are some of the dependencies commonly used. mPDF (Fixed) Vulnerability: Deserialization of untrusted data (similar to the 4Chan/Ghostscript exploit). Risk: Affects all versions lower than 7.1.8. aws/aws-sdk-php (Fixed) Path Traversal Affects versions lower t...
Post a Comment
Read more