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.
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
8
50 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
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
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
107 reads
CURATED FROM
IDEAS CURATED BY
Other curated ideas on this topic:
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...
Python data processing with pandas
Pandas is a Python language package, which is used for data processing. This is a very common basic programming library when we use Python language for machine learning programming. This article is an introductory tutorial to it. Pandas provide fast, flexible and expressive data structures with t...
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.
I agree to receive email updates