MySQL Performance Tips
In this page:
Using EXPLAIN to Analyze Queries
EXPLAIN shows you MySQL's actual execution plan for a query before it runs, revealing whether it's scanning an entire table row by row or efficiently using an index — the single most useful tool for diagnosing a slow query.
Example: Using EXPLAIN to Analyze Queries
CREATE TABLE orders (id INT, customer_id INT);
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;Adding Indexes on Queried Columns
Adding indexes on columns that show up in WHERE clauses, JOIN conditions, or ORDER BY lets MySQL jump straight to the relevant rows instead of scanning the whole table, similar to how a book's index saves you from reading every page.
Example: Adding Indexes on Queried Columns
CREATE TABLE orders (id INT, customer_id INT, order_date DATE);
CREATE INDEX idx_customer ON orders (customer_id);Avoid SELECT *
SELECT * pulls back every column in a table whether you need it or not, wasting both network bandwidth and server memory — listing only the specific columns you actually need keeps queries leaner and faster.
Example: Avoid SELECT *
CREATE TABLE users (id INT, name TEXT, email TEXT, bio TEXT);
-- Wasteful: pulls every column
SELECT * FROM users;
-- Better: only the columns actually needed
SELECT id, name FROM users;Optimizing Table Structures
Deleting rows over time can leave a table's storage fragmented with unused gaps. Running OPTIMIZE TABLE reclaims that wasted space and can noticeably improve read performance on tables with heavy delete activity.
Example: Optimizing Table Structures
CREATE TABLE logs (id INT, message TEXT);
DELETE FROM logs WHERE id < 100;
OPTIMIZE TABLE logs;Using Query Buffers Effectively
MySQL keeps frequently accessed data in memory buffers to avoid slow disk reads on every query, and checking the relevant system variables can reveal when those buffers are undersized and need tuning for your workload.
Example: Using Query Buffers Effectively
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: