SQL

Database Indices

Database Index Talk (2019)

Source: https://www.youtube.com/watch?v=HubezKbFL7E

• Assumes MySQL
• indexes can be implemented using a B-tree (like a binary tree, but with 1-3 keys per node)
• indexing makes reading faster and writing slower
• MySQL stores index in memory
• multi-column index on (col1, col2, col3) can index over (col1), (col1, col2), (col1, col2, col3) (no skipping) because indexing is hierarchical

access types (how you can retrieve data from the database, fastest to slowest)

• const / eq_ref: B-tree traversal to find a single value
• ref / range: B-tree traversal to find start point and scan within range
• index: traverses entire index
• all: full table scan (not use index)

SELECT SUM(total)
FROM orders
WHERE YEAR(created_at) = 2013;

Slow!

EXPLAIN (previous query)

• This reveals ALL access type because index is not on the year.
• Replace with WHERE created_at BETWEEN '2013-01-01 00:00:00' and '2013-12-31 23:59:59'

still slow because it needs to retrieve total from the table per row in the index

• solution: index on (created_at, total)

Overall takeaway

• design your indices for a specific set of queries

Extra Knowledge from Googling

• CREATE INDEX index ON table(column1, column2) INCLUDE(column3)
  • index columns are sorted while included columns are not