Performance Tips
Efficient SQL matters, especially as your database grows.
In this lesson, you’ll learn how to optimize queries using indexes, execution plans, and smart design strategies.
1. Use Indexes
An index is like a table of contents for your database.
They help the system locate rows faster, especially in large tables.
Create an index
CREATE INDEX idx_student_name ON students(name);
When to use indexes:
You can use indexes in the following cases:
- Columns used in
WHERE,JOIN, orORDER BY - Tables with lots of rows and frequent lookups
Avoid excessive indexing. Indexes speed up reads but slow down writes.
2. Avoid SELECT *
Selecting only the required columns reduces I/O and memory usage.
SELECT * vs SELECT name, grade
-- Not recommended
SELECT * FROM students;
-- Better
SELECT name, grade FROM students;
3. Use EXPLAIN or EXPLAIN QUERY PLAN
Use EXPLAIN to understand how a query runs behind the scenes.
Use EXPLAIN
EXPLAIN QUERY PLAN
SELECT name FROM students WHERE grade > 90;
This helps you identify:
- Full table scans (slow)
- Use of indexes (fast)
- Inefficient joins or sorts
4. Filter Early, Join Later
Reduce rows before joining to improve query performance.
Early filtering example
-- Better approach
SELECT *
FROM (SELECT * FROM orders WHERE status = 'paid') AS o
JOIN customers c ON o.customer_id = c.id;
5. Use LIMIT During Development
To avoid expensive mistakes when testing queries:
Limit 100 rows
SELECT * FROM logs LIMIT 100;
Summary
| Strategy | Benefit |
|---|---|
| Indexes | Faster searches and joins |
Avoid SELECT * | Reduces unnecessary data scans |
Use EXPLAIN | Reveal performance issues |
| Filter before join | Smaller, faster joins |
Use LIMIT in dev | Prevent long-running queries |
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.