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
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)
9
107 reads
MORE IDEAS ON THIS
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 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
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
50 reads
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
19 reads
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&...
7
19 reads
CURATED FROM
CURATED BY
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.
I agree to receive email updates