Essential MySQL Query Optimization Tips to Avoid Full Table Scans

1. Indexes to Prevent Full Table Scans

Always create indexes on columns used in WHERE and ORDER BY clauses. Proper indexing eliminates the need for a full‑table scan.

2. Avoid NULL Checks and Use NOT NULL

Prefer NOT NULL columns or sentinel values (e.g., 0, -1) instead of testing for NULL in the WHERE clause.

3. Skip Inefficient Comparison Operators

MySQL only uses indexes with <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE. Avoid != or <> as they force a table scan.

4. Reduce OR Conditions

Using OR in WHERE often disables index usage. Replace with UNION ALL queries, e.g.:

SELECT id FROM t WHERE num=10 UNION ALL SELECT id FROM t WHERE num=20;

5. Use IN/NOT IN Sparingly

Prefer BETWEEN for continuous ranges and avoid IN on large sets. Example:

SELECT id FROM t WHERE num BETWEEN 1 AND 3;

6. Optimize LIKE Patterns

Leading wildcards prevent index use. Use LIKE 'abc%' (indexable) instead of LIKE '%abc%'. For complex text search, consider full‑text indexing.

7. Avoid Functions and Expressions on Indexed Columns

Any function or expression (e.g., SUBSTRING(col,1,4), CONVERT(...)) forces a scan because the optimizer cannot use the index.

8. Prefer EXISTS Over IN

Rewrite IN subqueries with EXISTS for better performance:

SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.num = a.num);

9. Index Management

Limit a table to about six indexes; evaluate each for necessity.

Avoid indexing columns with low selectivity or high duplication.

Prefer single‑column indexes over wide composite indexes unless the composite fields are always used together with AND.

Do not index large text columns.

10. Clustered Index Updates

Updating clustered‑index columns forces row reordering and is expensive. Only use clustered indexes when updates are rare.

11. Choose Appropriate Data Types

Use numeric types for numeric data; avoid storing numbers as strings.

Prefer VARCHAR / NVARCHAR over fixed‑length CHAR / NCHAR to save space and improve search speed.

Define a primary key as an unsigned INT with AUTO_INCREMENT.

12. Avoid SELECT *

Specify only required columns to reduce I/O and network traffic.

13. Limit Result Sets

Do not return massive data sets unless absolutely necessary; consider pagination or filtering.

14. Use Table Aliases

Alias tables and prefix column names to reduce parsing time and avoid ambiguity.

15. Temporary Tables and Table Variables

Use temporary tables to store intermediate results, but prefer table variables when possible because they reside in memory and are faster.

16. NOLOCK (Read‑Uncommitted) Guidance

Only use for read‑only queries that do not affect data integrity.

Avoid on tables with frequent page splits.

17. Limit Joins and Subqueries

Keep the number of joined tables below five; replace deep subqueries with temporary tables or table variables.

18. Pre‑compute Results

Store frequently needed results in a table and query that table instead of recomputing each time.

19. Rewrite OR with UNION for Index Use

When OR conditions prevent index usage, split into separate queries combined with UNION ALL.

20. Order IN List Values

Place the most frequent values first in an IN list to reduce comparison work.

21. Push Processing to the Server

Use stored procedures to execute logic close to the data, reducing network overhead.

22. Thread Configuration

If memory permits, set max_connections + 5 threads; otherwise rely on the built‑in thread pool.

23. Table Join Order

When multiple tables are joined, start with the table that returns the fewest rows (driving table) to minimize intermediate result size.

24. Use EXISTS Instead of COUNT(1)

Checking existence with EXISTS is faster than COUNT(1) or COUNT(*).

25. Prefer >= Over >

Using >= can sometimes enable index usage where > does not.

26. Index Creation Guidelines

Primary keys and foreign keys must be indexed.

Tables with >300 rows should have indexes on frequently queried columns.

Index high‑selectivity, small columns.

Avoid redundant indexes.

27. Backup Strategies

Stop replication before backup, use mysqldump --opt, disable foreign‑key checks during import, and monitor binary logs for consistency.

28. Query Cache

Enable MySQL query cache for repeated identical queries to reduce execution time.

29. EXPLAIN

Run EXPLAIN SELECT … to see how MySQL plans to execute a query and identify bottlenecks.

30. LIMIT 1 for Single‑Row Queries

Adding LIMIT 1 stops the engine after finding the first matching row, saving time.

31. Choose the Right Storage Engine

MyISAM : read‑heavy, few writes, low concurrency.

InnoDB : transactional, high concurrency, supports row‑level locking.

32. Optimize Data Types

Use the smallest integer type that fits (e.g., MEDIUMINT instead of INT).

Prefer TIMESTAMP over DATETIME when possible.

Define ENUM for low‑cardinality text fields.

33. String Column Choices

Use CHAR only for fixed‑length short strings; otherwise VARCHAR is more space‑efficient.

34. Avoid Operations on Columns

Any operation (function, calculation, conversion) on a column forces a full scan; keep such logic on the right‑hand side of the comparison.