Avoid full table scan in MySQL

Stats

SHOW GLOBAL STATUS LIKE 'Select%';

Counter Select_scan shows how many full table scans were done since last MySQL restart.

Counter Select_full_join is even worse as MySQL has to perform a full table scan against a joined table which is even slower.

With that being said, we need to try the best to avoid full table scan when writing queries. 

Use index

Apart from PK and foreign keys, add index to columns

• Columns frequently used to join tables
• Columns that are frequently used as conditions in a query
• Columns that have a high percentage of unique values

Without index on the column appears in where clause or sort by, MySQL will walk through the entire table to filter rows one-by-one.

Best practice

Avoid using function or math

SELECT * FROM table WHERE func(a) = 100
SELECT * FROM table WHERE a + 3 < 100

Avoid using Not equal and NOT IN

SELECT * FROM table WHERE a <> 1
SELECT * FROM table WHERE a NOT IN (1,2,3)

Avoid Bitwise on numeric column

SELECT * FROM table WHERE (a & 4) = 0

Avoid putting a wild-card before the first characters of the search criteria

SELECT * FROM table WHERE a LIKE '%abc'

Avoid the OR Operator

SELECT * FROM table WHERE a = 1 OR a = 2 OR a = 3

Try to replace it with an IN operator, something like SELECT * FROM table WHERE a IN (1,2,3)

Avoid using Having

Avoid using Order by if possible

Avoid using Group by if possible

Avoid using DISTINCT if possible

Avoid using ORDER BY RAND()

Avoid SELECT COUNT(*) FROM table

InnoDB doing a full table scan for this statement.