PHP Academy · Lesson

Optimizing Database Queries

Identify N+1 queries, add indexes, and reduce query count.

Identify Slow Queries

Enable the MySQL slow query log and set a threshold. Review queries taking longer than expected.

# my.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5  # log queries over 500ms

Use EXPLAIN to understand how MySQL executes a query and whether indexes are used.

-- Run in MySQL:
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = "pending";
-- Look for: type=ALL (full scan), key=NULL (no index used)