Composite Indexes
SQL: lesson 13 of 15
Column order decides which queries the index can serve.
Lesson 13 of 15 · 6 min
Composite Indexes
Step 1 of 9
rows readall 3
The query wants Leeds, ordered by day, returning day and bikes. Nothing is indexed yet.
The Idea
A composite index sorts by its first column, then by the second within that. So it serves a filter on the leading column, or on a leading prefix, and nothing else. Add every column the query reads and it becomes covering: the engine answers from the index and never touches the table.
Real-World Example
A dock ledger filed by city, and within each city by date. Finding Leeds on the 4th is two flicks. Finding everything that happened on the 4th across all cities means reading the whole ledger.
The Code
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE dock (city TEXT, day TEXT, bikes INT);
INSERT INTO dock VALUES ('Leeds','Mon',12),('Leeds','Tue',9),('Hull','Mon',4);""")
q = "SELECT day, bikes FROM dock WHERE city = 'Leeds' ORDER BY day"
print(db.execute("EXPLAIN QUERY PLAN " + q).fetchall()[0][3]) # SCAN dock
db.execute("CREATE INDEX i_cov ON dock(city, day, bikes)")
print(db.execute("EXPLAIN QUERY PLAN " + q).fetchall()[0][3])
# SEARCH dock USING COVERING INDEX i_cov (city=?)
print(db.execute(
"EXPLAIN QUERY PLAN SELECT bikes FROM dock WHERE day='Mon'").fetchall()[0][3])
# SCAN dock -- day is not the leading columnYour turn
Put the steps in the right order.
- Widen it to cover the columns the query returns
- Read the query plan and find the SCAN
- Confirm the plan now says COVERING INDEX
- Put the equality-filtered column first in the index
Mini quiz
1 / 3