MySQL or MariaDB slow query basics

Topic: Databases core

Enable slow query log and use EXPLAIN to fix slow MySQL queries. Add indexes on WHERE and JOIN columns. Use this when the database is slow.

Intent: How-to

Quick answer

  • Set slow_query_log = 1 and long_query_time. Queries over the threshold are logged.
  • EXPLAIN the slow query. Add indexes on filtered and joined columns. ANALYZE TABLE.
  • Verify improvement; remove unused indexes.

Prerequisites

Steps

  1. Enable slow log

    slow_query_log = 1; long_query_time = 2. Restart or SET GLOBAL.

  2. Find and fix

    Read slow log. EXPLAIN query; add indexes; ANALYZE TABLE.

  3. Verify

    Re-run query; document; drop unused indexes.

Summary

Enable slow log; EXPLAIN and add indexes; verify.

Prerequisites

Steps

Step 1: Enable slow log

Set slow_query_log and long_query_time.

Step 2: Find and fix

Inspect slow log; EXPLAIN; add indexes; ANALYZE.

Step 3: Verify

Re-run; document; drop unused indexes.

Verification

  • Query time reduced.

Troubleshooting

Still slow — Check locks and I/O. Too many indexes — Remove unused.

Next steps

Continue to