SQL - Indexes (Just filter or join on indexed columns)

This lesson on SQL - Indexes (Just filter or join on indexed columns) is hands-on and example-driven. You will optimize query execution speed by creating, inspecting, and dropping single and multi-column B-tree indexes in MySQL. You will also evaluate read-versus-write trade-offs and leverage the leftmost prefix rule to support combined filtering and joining workflows.

What You'll Be Able To Do

• Identify current table indexes using administrative inspection commands.
• Create single-column and multi-column indexes on high-frequency search fields.
• Evaluate table workload characteristics to balance query speedups against write penalties.
• Apply the leftmost prefix rule to ensure multi-column queries utilize existing indexes.
• Drop redundant single-column indexes after implementing composite indexes.

Detailed Concept Walkthrough

1. B-Tree Indexes and Query Performance

An index is a specialized B-tree data structure that allows the database engine to locate target records rapidly without reading every row. Without an index, the database executes a full sequential scan from top to bottom.

• Mechanism: Sequential scans check every single record across a column until the target condition resolves. Indexes instead navigate a balanced tree structure to pinpoint matched records in logarithmic time.
• Under the Hood: Primary key constraints automatically generate a clustered B-tree index upon table creation. Searching via non-indexed columns forces MySQL into linear disk and memory reads.
• Workload Nuance: Small datasets show negligible performance differences between scans and index lookups. However, multi-million-row enterprise tables experience massive latency reductions when filtering on indexed attributes.

-- View automatically generated and existing custom indexes
SHOW INDEXES FROM customers;

-- Query filtering on indexed primary key
SELECT * FROM customers 
WHERE customer_id = 1042;

Key Takeaway: Indexes transform costly full-table sequential scans into fast B-tree tree traversals for high-cardinality searches.

2. Read vs Write Index Trade-Offs

Indexes drastically accelerate data retrieval operations (SELECT) while imposing a write latency penalty on data modification operations (INSERT, UPDATE, DELETE).

• Mechanism: Whenever a row is inserted, deleted, or its indexed column is updated, the database must synchronously restructure the underlying B-tree. This overhead accumulates rapidly in write-heavy environments.
• Workload Selection: Read-heavy tables with infrequent updates (such as a customer registry) are prime candidates for indexing. Write-heavy tables (such as continuous payment transactions) suffer noticeable performance degradation when indexed excessively.
• Best Practice: Evaluate query frequency versus modification frequency before applying indexes to prevent crippling write throughput.

-- Good candidate: Read-heavy table with infrequent writes
CREATE INDEX last_name_idx 
ON customers (last_name);

-- Fast search query leveraging the index
SELECT * FROM customers 
WHERE last_name = 'Puff';

Key Takeaway: Index read-heavy lookup tables frequently, but minimize index creation on high-throughput transactional write tables.

3. Multi-Column Indexes and Leftmost Prefix

Multi-column (composite) indexes index multiple attributes together in a strict left-to-right sequence. Queries must filter using the leftmost column to utilize the composite index tree.

• Leftmost Prefix Rule: MySQL evaluates composite index columns strictly from left to right. A composite index on (last_name, first_name) accelerates queries filtering by last_name or (last_name, first_name), but completely ignores queries filtering solely by first_name.
• Redundancy Optimization: A composite index (A, B) automatically covers lookups on column A alone. Maintaining an additional standalone index on A wastes storage and causes unnecessary write overhead.
• Index Maintenance: Redundant or obsolete indexes should be explicitly removed using ALTER TABLE ... DROP INDEX to maintain optimal write performance.

-- Create a multi-column composite index
CREATE INDEX last_name_first_name_idx 
ON customers (last_name, first_name);

-- Drop redundant standalone index covered by the composite index
ALTER TABLE customers 
DROP INDEX last_name_idx;

-- Uses index: filters on leftmost column
SELECT * FROM customers WHERE last_name = 'Puff';

-- Uses index: filters on both columns in sequence
SELECT * FROM customers WHERE last_name = 'Puff' AND first_name = 'Poppy';

-- Fails to use index: skips leftmost column
SELECT * FROM customers WHERE first_name = 'Poppy';

Key Takeaway: Define composite indexes in order of query dependency, and rely on the leftmost prefix rule to eliminate redundant single-column indexes.

Topics Covered in SQL - Indexes (Just filter or join on indexed columns)

1. Index Concept and Data Structure (0:00 - 0:25) — B-tree data structures accelerate column searches compared to default sequential full table scans.
2. Read vs Write Workload Trade-Offs (0:25 - 0:55) — Search speed gains come at the cost of slower insert and update execution times.
3. Inspecting and Creating Single Indexes (0:55 - 1:35) — Existing table indexes are viewed and a new single-column index is added to last_name.
4. Multi-Column Indexes and Leftmost Prefix (1:35 - 2:15) — Composite indexes require a defined column order to leverage MySQL leftmost prefix searching.
5. Dropping Redundant Indexes (2:15 - 2:50) — Redundant single-column indexes are removed using the ALTER TABLE DROP INDEX statement.

SQL Cheat Sheet

• SHOW INDEXES FROM table_name — Lists all indexes existing on a specified table

  SHOW INDEXES FROM customers;
  

• CREATE INDEX index_name ON table_name (column) — Creates a single-column index on a table

  CREATE INDEX last_name_idx ON customers (last_name);
  

• CREATE INDEX index_name ON table_name (col1, col2) — Creates a multi-column index honoring column order

  CREATE INDEX last_name_first_name_idx ON customers (last_name, first_name);
  

• ALTER TABLE table_name DROP INDEX index_name — Deletes an existing index from a table

  ALTER TABLE customers DROP INDEX last_name_idx;
  

Comparison Table

Common Pitfalls

• Mistake: Indexing write-heavy tables with continuous updates. Avoid: Reserve indexes for read-heavy tables to avoid severe update latency.
• Mistake: Querying non-leading columns in a composite index. Avoid: Structure WHERE clauses to include the leftmost column defined in the index.
• Mistake: Retaining duplicate single-column indexes alongside composite equivalents. Avoid: Drop standalone single-column indexes if a composite index begins with that column.

FAQs

• Does MySQL automatically create any indexes? Yes, MySQL automatically creates a primary key index when you define a primary key column.
• Can a multi-column index on (last_name, first_name) speed up searches on first_name alone? No, MySQL requires the leftmost prefix (last_name) in the filter condition to navigate the composite index.
• Why is my query using an index not noticeably faster on my local test database? Small datasets have so few records that sequential scans finish instantaneously, whereas benefits become obvious on millions of rows.