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

SQL - Use The Index

use-the-index-luke.com

|

6 IDEAS

3

6

Keep reading for FREE

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)

8

83 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

7

36 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

7

14 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. 

7

10 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'

7

8 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

7

9 reads

CURATED BY

adn

Love problem solving and sport.

Ready for the next level?

Read Like a Pro

stash-superman-illustration

Explore the World’s

Best Ideas

200,000+ ideas on pretty much any topic. Created by the smartest people around & well-organized so you can explore at will.

An Idea for Everything

Explore the biggest library of insights. And we've infused it with powerful filtering tools so you can easily find what you need.

Knowledge Library

Powerful Saving & Organizational Tools

Save ideas for later reading, for personalized stashes, or for remembering it later.

# Personal Growth

Take Your Ideas

Anywhere

Organize your ideas & listen on the go. And with Pro, there are no limits.

Listen on the go

Just press play and we take care of the words.

Never worry about spotty connections

No Internet access? No problem. Within the mobile app, all your ideas are available, even when offline.

Get Organized with Stashes

Ideas for your next work project? Quotes that inspire you? Put them in the right place so you never lose them.

Join

2 Million Stashers

4.8

Stars

5,740 Reviews

App Store

4.7

Stars

72,690 Reviews

Google Play

Sean Green

Great interesting short snippets of informative articles. Highly recommended to anyone who loves information and lacks patience.

Ashley Anthony

This app is LOADED with RELEVANT, HELPFUL, AND EDUCATIONAL material. It is creatively intellectual, yet minimal enough to not overstimulate and create a learning block. I am exceptionally impressed with this app!

Shankul Varada

Best app ever! You heard it right. This app has helped me get back on my quest to get things done while equipping myself with knowledge everyday.

samz905

Don’t look further if you love learning new things. A refreshing concept that provides quick ideas for busy thought leaders.

Jamyson Haug

Great for quick bits of information and interesting ideas around whatever topics you are interested in. Visually, it looks great as well.

Laetitia Berton

I have only been using it for a few days now, but I have found answers to questions I had never consciously formulated, or to problems I face everyday at work or at home. I wish I had found this earlier, highly recommended!

Giovanna Scalzone

Brilliant. It feels fresh and encouraging. So many interesting pieces of information that are just enough to absorb and apply. So happy I found this.

Ghazala Begum

Even five minutes a day will improve your thinking. I've come across new ideas and learnt to improve existing ways to become more motivated, confident and happier.

Read & Learn

20x Faster

without
deepstash

with
deepstash

with

deepstash

Access to 200,000+ ideas

Access to the mobile app

Unlimited idea saving & library

Unlimited history

Unlimited listening to ideas

Downloading & offline access

Personalized recommendations

FAQ

Claim Your Limited Offer

Get Deepstash Pro

BLACK FRIDAY

75% OFF | 1-Year Pro Subscription

Claim Offer