SQL - Use The Index

Order of the columns matter.

The index is useless if the first column doesn't appear in the WHERE clause.

Example 1:

An index on (t, x) will not be used in this query:

• SELECT * FROM table WHERE x = 11

Example 2:

However an index on (x, t) will be used in both of these queries:

SELECT * FROM table WHERE x = 11

and

SELECT * FROM table WHERE t = 9 and x = 11

Wrapping the indexed column in a function nullifies the index almost completely. 

Index:

CREATE INDEX e_idx ON users (email) 

Query:

SELECT COUNT(*) 

FROM users  

WHERE UPPER(email) = '[email protected]'

This will read the full index end to end which is only slightly better than reading full table. 

When it can be used:

1. Exact matches:

• SELECT * FROM table WHERE x = 'sql'

2. Using wild card character (% or _) at the end:

• SELECT * FROM table WHERE x LIKE 'sq%'

3. Less effective, but still useful, using wild card character in the middle:

• SELECT * FROM table WHERE x LIKE 's_l'

When it is useless:

When wild card character is used in the beginning:

• SELECT * FROM table WHERE x LIKE '%ex'

• Avoid using functions in WHERE clause on indexes when it is not needed
• Use LIMIT when partial results are needed
• Order of columns in Multi-Column Indexes is important
• Extra conditions in WHERE clause can ruin the use of index-only scan, which doesn't access the actual table at all. Even if the filter limits the results more, it requires more processing
• Use LIKE filters wisely as explained in the previous idea