MySQL SQL and Index Optimization Techniques

Before diving into SQL optimization, the article outlines MySQL's internal architecture, including the connector, query cache (disabled by default in MySQL 8.0), parser, optimizer, and executor.

SQL Statement Optimization

Avoid subqueries; rewrite them as JOINs. Example:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE name = 'chackca')

can become SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id.

Replace OR with IN: SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30 → SELECT * FROM t WHERE id IN (10,20,30).

Use appropriate LIMIT pagination; for large offsets, fetch the next page by primary key instead of LIMIT m,n.

Disable unnecessary ORDER BY and GROUP BY sorting by adding ORDER BY NULL when ordering is not required.

Prefer UNION ALL over UNION when duplicate rows are impossible.

Avoid random row selection with ORDER BY RAND() as it bypasses indexes.

Convert multiple single‑row INSERTs into a batch INSERT.

Never use SELECT *; list only needed columns to enable covering indexes.

Distinguish between IN and EXISTS: IN is efficient when the outer table is large and the inner table is small; EXISTS is better when the outer table is small.

Optimize GROUP BY by adding ORDER BY NULL and ensuring the grouping columns use indexes, avoiding temporary tables and filesorts.

Use numeric fields for numeric data to reduce comparison overhead.

Optimize JOINs by choosing the smaller result set as the driving table, adding indexes on join columns, and adjusting join_buffer_size when necessary.

Index Optimization and Avoiding Index Failure

Follow the left‑most prefix rule for multi‑column indexes.

Do not apply functions or arithmetic on indexed columns in WHERE clauses; rewrite expressions to keep the column on the left side.

Be aware of implicit type conversion that can invalidate indexes; use proper literals (e.g., quote strings for VARCHAR columns).

Prefer covering indexes (query columns match index columns) to avoid full row lookups.

Negative conditions (!=, <>, NOT IN, NOT EXISTS, NOT LIKE) often prevent index usage.

LIKE patterns should have the wildcard at the end (e.g., col LIKE 'abc%') to use indexes.

Avoid OR unless both sides are indexed; otherwise the optimizer may fall back to a full scan.

In composite indexes, place the most selective columns first.

Use prefix indexes for long VARCHAR columns to save space, but note they cannot support ORDER BY or covering scans.

Check the type column in EXPLAIN output; aim for range, ref, or const for efficient index usage.

The article also briefly mentions database schema normalization to third normal form (3NF) and BCNF, system configuration tuning, and hardware considerations for overall performance.