MySQL Optimization Tips Frequently Asked in Interviews

When optimizing MySQL queries, start by using EXPLAIN to examine the execution plan and focus on the type (prefer range over all), key (ensure an index is chosen), key_len , rows (estimated row count), and extra (watch for Using filesort or Using temporary).

Keep IN lists short; for many consecutive values use BETWEEN or replace with a join. Always specify column names instead of SELECT * to avoid unnecessary I/O and enable covering indexes.

If only one row is needed, add LIMIT 1 to encourage a const access type. Minimize sorting when the sort column lacks an index, and avoid OR conditions on non‑indexed columns—use UNION ALL or a separate join instead.

Prefer UNION ALL over UNION when duplicate rows are impossible, as the former skips the costly distinct‑merge step.

Avoid ORDER BY RAND(); rewrite it using a join with a derived table that generates random numbers, for example:

SELECT id FROM `table_name` t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM `table_name`) AS nid) t2 ON t1.id > t2.nid LIMIT 1000;

Distinguish IN vs. EXISTS: EXISTS drives the outer table and is better when the outer table is small; IN is suitable when the inner table is small.

Use full‑text indexes for pattern searches like LIKE '%name%', because ordinary indexes cannot be used. Create them with:

ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name`(`user_name`);

When querying, use the full‑text match syntax:

SELECT id, fnum, fdst FROM table_name WHERE MATCH(user_name) AGAINST('zhangsan' IN BOOLEAN MODE);

Avoid expressions on indexed columns in WHERE clauses (e.g., age*2 = 36)—rewrite them as simple comparisons ( age = 18) so the optimizer can use the index.

Prevent implicit type conversions by matching parameter types to column types in predicates.

Follow the left‑most prefix rule for composite indexes: the index can be used only for the leftmost columns in the defined order.

When necessary, force the optimizer to use a specific index with FORCE INDEX.

Be aware that range conditions (e.g., BETWEEN, >, <) invalidate subsequent index columns.

For join optimization, prefer INNER JOIN (MySQL automatically picks the smaller table as the driver). Use STRAIGHT_JOIN to force a specific join order when needed, especially with GROUP BY or ORDER BY that cause Using filesort or Using temporary.

Implement efficient pagination by remembering the last retrieved id and querying with WHERE id > last_id LIMIT 20 instead of large offset LIMIT values.

For very large result sets, split the query into smaller time‑range segments and merge the results to reduce row scans.

Overall, careful index selection, avoiding costly operations, and rewriting queries to match the optimizer’s strengths lead to significant performance gains.