1. Use SELECT with Specific Columns Instead of SELECT *
SELECT * fetches all columns, even unused ones, increasing memory and bandwidth usage.
General Syntax:
SELECT column1, column2 FROM table_name;
1. Example:
a. For particular column:-
SELECT name, age FROM students;
Output:-
b. To display every columns:-
SELECT * FROM students;
Output:-
2. Use WHERE Clause to Filter Early
Use WHERE to reduce the number of rows MySQL needs to work with. The earlier you filter data, the faster other operations (like JOINs or GROUP BY) will be.
General Syntax:
SELECT columns FROM table_name WHERE condition;
Sample table:-
Example:
SELECT * FROM orders WHERE status = ‘Delivered’;
Output:-
3. Avoid Complex Subqueries — Use JOINs Where Possible
JOINs are often faster than subqueries, especially when indexing is used. Subqueries can result in temporary tables and higher memory usage.
General Syntax (JOIN):
SELECT columns FROM table1
JOIN table2 ON table1.column = table2.column;
Sample table:-
Example (Join):
SELECT employees.name, departments.name
FROM employees
JOIN departments ON employees.dept_id = departments.id;
Output:-
4. Use LIMIT for Pagination and Sampling
LIMIT reduces load by returning only a subset of rows. This is very useful in dashboards, reports, or lazy-loading systems.
General Syntax:
SELECT columns FROM table_name LIMIT count OFFSET start;
Sample table:-
Example:
SELECT name FROM users LIMIT 10 OFFSET 20;
Output:-
( This fetches 10 records starting from the 21st row. )
5. Optimize ORDER BY and GROUP BY
Use ORDER BY and GROUP BY on indexed columns to avoid temporary files and table scans.
General Syntax:
SELECT columns FROM table_name ORDER BY column [ASC|DESC];
SELECT column, COUNT(*) FROM table_name GROUP BY column;
Sample Employee Table:-
a. GROUP BY with COUNT Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;
Output:-
b. ORDER BY with DESC Example:-
SELECT name, salary FROM employees ORDER BY salary DESC;
Output:-
6. Use Covering Indexes
A covering index includes all the columns needed for the query, so MySQL doesn’t need to fetch data from the table itself.
General Syntax (index creation):
CREATE INDEX idx_col1_col2 ON table_name(col1, col2);
Sample student table:-
Example:
SELECT name, age FROM students WHERE grade = ‘A’;
Output:-
7. Avoid Wildcards at the Start of LIKE Patterns
Using % at the beginning prevents index usage and causes full table scans.
Sample Table:-
a. Uses Index:-
SELECT name FROM customers WHERE name LIKE ‘A%’;
Output:-
b. No use of Index:-
SELECT name FROM customers WHERE name LIKE ‘%an’;
Output:-
8. Optimize IN and OR Clauses
Use indexed columns in IN and OR, or rewrite using UNION if performance drops.
Example:-
SELECT * FROM students WHERE id IN (1, 2, 3);
Alternative using UNION:-
SELECT * FROM students WHERE id = 1
UNION
SELECT * FROM students WHERE id = 2
UNION
SELECT * FROM students WHERE id = 3;
Output:-
(The output would be same for both using IN or UNION)
9. Analyze Queries with EXPLAIN or ANALYZE
Syntax:
EXPLAIN SELECT …;
In MySQL 8.0+:
ANALYZE FORMAT=TRADITIONAL SELECT …;
( This shows row estimates, type of scans, index usage, cost, etc., helping you rewrite slow queries. )