Performance Tips
Efficient SQL matters, especially as your database grows.
In this lesson, you'll learn how to optimize queries through the use of indexes, execution plans, and thoughtful design strategies.
1. Use Indexes
An index is like a table of contents for your database.
It helps the system find 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 *
Fetching only the needed columns reduces I/O and memory use.
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 detect:
- Full table scans (slow)
- Use of indexes (fast)
- Inefficient joins or sorts
4. Filter Early, Join Later
Always reduce the number of rows before joining. This improves speed.
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 * | Less data scanned |
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.