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

3

6

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)

10

112 reads

Single vs Concatenated Indexes

Single vs Concatenated Indexes

Terminology:

  • 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

9

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

and

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

9

22 reads

Functions on Indexes

Functions on Indexes

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@mail.com'

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

8

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'

8

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

8

24 reads

IDEAS CURATED BY

adn

Love problem solving and sport.

Read & Learn

20x Faster

without
deepstash

with
deepstash

with

deepstash

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.

Email

I agree to receive email updates