Functions on Indexes - Deepstash

Keep reading for FREE

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

19 reads

MORE IDEAS ON THIS

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, bu...

7

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

8

19 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 claus...

8

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

7

18 reads

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

9

105 reads

CURATED FROM

CURATED BY

adn

Love problem solving and sport.

More like this

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

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

5,740 Reviews

App Store

4.7

72,690 Reviews

Google Play

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.

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!

Sean Green

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

samz905

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

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!

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.

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.

Jamyson Haug

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

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

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