SQL - Use The Index - Deepstash
SQL - Use The Index

SQL - Use The Index

Curated from: use-the-index-luke.com

Ideas, facts & insights covering these topics:

6 ideas


254 reads



Explore the World's Best Ideas

Join today and uncover 100+ curated journeys from 50+ topics. Unlock access to our mobile app with extensive features.

Index Overview

Index Overview

What is it?:

  • A stand-alone database structure
  • Redundant information, well ordered, that acts as a map/reference to the table

What is it good for?:

  • Makes the SELECT/retrieval queries fast

Does it have any disadvantage?:

  • It requires disk space
  • It requires RAM to take advantage of it entirely
  • Causes maintenance overhead for write operations with each INSERT/UPDATE/DELETE

Creating index syntax in POSTGRESQL:

CREATE INDEX e_idx ON users (email)


112 reads

Single vs Concatenated Indexes

Single vs Concatenated Indexes


  • Single Index = Single-Column Index
  • Concatenated Index = Multi-Column Index

How indexes work:

  • Index is used to find data fast, therefore it looks for the WHERE clause that defines the search condition

When to use Single-Column Indexes:

  • When most of the time that indexed column is used in the WHERE clause

Index on (x)

SELECT * FROM table WHERE x = 11

When to use Multi-Column Indexes:

  • When more columns are used in the WHERE clause

Index on (t, x):

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


55 reads

Order in Multi-Column Index Matters

Order in Multi-Column Index Matters

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


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


22 reads

Functions on Indexes

Functions on Indexes

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


CREATE INDEX e_idx ON users (email) 



FROM users  

WHERE UPPER(email) = 'email@mail.com'

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


23 reads

Index on Text Columns

Index on Text Columns

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'


18 reads

What to keep in mind?

What to keep in mind?

  • 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


24 reads



Love problem solving and sport.

Read & Learn

20x Faster





Personalized microlearning

100+ Learning Journeys

Access to 200,000+ ideas

Access to the mobile app

Unlimited idea saving

Unlimited history

Unlimited listening to ideas

Downloading & offline access

Supercharge your mind with one idea per day

Enter your email and spend 1 minute every day to learn something new.


I agree to receive email updates