Optimizing Queries with Single-Column and Composite Indexes

Creating a Single-Column Index

The simplest and most common type of index is one created on a single column. This is the go-to solution when your queries frequently filter on a specific column, such as a user's ID, email address, or a foreign key.

The syntax is straightforward:

CREATE INDEX index_name ON table_name (column_name);

For example, if you often run queries to find a user by their email, you would create an index like this:

CREATE INDEX idx_users_email ON users (email);

Once this index exists, a query like SELECT * FROM users WHERE email = 'jane.doe@example.com'; will no longer scan the entire users table. Instead, the database will use the idx_users_email B-Tree to quickly find the pointer to the correct row, just as we discussed in the last lesson.

Let's see a practical demonstration of creating a single-column index and observing its impact.

As the video showed, adding the right index can reduce query time by an order of magnitude, from tens ofmilliseconds to just a few. This is the direct result of replacing a full scan with a targeted index seek.

From One Column to Many: Composite Indexes

What happens when your queries filter on more than one column? For instance, a query to find all pending orders for a specific user:

SELECT * FROM orders 
WHERE user_id = 12345 AND status = 'pending';

While separate indexes on user_id and status might help a little (some databases can merge two indexes), the most efficient solution is a composite index (or multi-column index). This is a single index built on two or more columns.

The syntax is a simple extension of what you've already seen:

CREATE INDEX index_name ON table_name (column1, column2, ...);

For our query, we could create:

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

This creates a single B-Tree where the keys are sorted first by user_id, and then for each user_id, they are sorted by status.

The Golden Rule: Column Order in Composite Indexes is Critical

This is the most important concept to master when working with composite indexes. The order in which you list the columns in your CREATE INDEX statement matters immensely.

A database can use a composite index on (col_A, col_B, col_C) to efficiently serve queries that filter on:

• col_A
• col_A AND col_B
• col_A AND col_B AND col_C

This is known as the left-prefix rule. The index can only be used effectively if the query's WHERE clause uses the columns from the left side of the index definition without skipping any.

It cannot be used efficiently for queries that filter only on:

• col_B
• col_C
• col_B AND col_C

This is because the index's B-Tree is sorted lexicographically, like words in a dictionary. If the index is on (last_name, first_name), you can easily look up "Smith, John". But you can't use it to efficiently find everyone whose first name is "John", because their entries are scattered all over the index under different last names.

Let's watch a video that explains this crucial rule and its implications.

To solidify this concept, let's review a detailed written explanation.

Strategy for Ordering Columns in a Composite Index

Now that you understand that order matters, how do you decide on the correct order? Here are the key strategic guidelines:

1. Equality First, Range Second: Place columns that are used with equality operators (=, IN) at the beginning of the index. Place columns used with range operators (>, <, BETWEEN, LIKE 'prefix%') after the equality columns. The database can use the index to seek to the start of a range, but it generally cannot use any subsequent columns in the index for seeking after that point.

2. Selectivity: As a general rule, place columns with higher selectivity (more unique values) first. For example, user_id is much more selective than a status column that only has 'pending', 'shipped', and 'delivered' as values. Putting the highly selective column first allows the database to narrow down the number of potential rows much more quickly.

3. Logical Hierarchy (An Exception to Selectivity): Sometimes, your data has a natural hierarchy, like Country -> State -> City. Even if City is the most selective column, if your queries always filter down from Country to State, your index should follow that logical hierarchy: (Country, State, City). This allows the index seek to follow the same logical path as your query, which can be far more efficient.

Demonstration: Creating an Optimized Composite Index

Let's conclude by watching the creation of a composite index and seeing the powerful results firsthand.

The result is striking. The query time drops from a noticeable delay to being virtually instantaneous. This is because the database could satisfy the entire query—the filtering and the sorting—directly from the highly optimized, pre-sorted composite index. This is known as a covering index, a concept we'll touch on more later.

SELECT product_id, name, price
FROM products
WHERE category = 'electronics'
  AND in_stock = TRUE
ORDER BY date_added DESC;

CREATE INDEX idx_products_cat_stock_date 
ON products (category, in_stock, date_added);

Conclusion

Today you've moved from the theory of indexes to the practice of creating them. You now have the syntax and, more importantly, the strategic framework to start optimizing real-world queries.

Key Takeaways:

• You can create a single-column index with CREATE INDEX ... ON table (column); to speed up queries filtering on that one column.
• You can create a composite index on multiple columns to optimize queries with multi-part WHERE clauses.
• The left-prefix rule is paramount: the order of columns in a composite index dictates which queries can use it effectively.
• To design an effective composite index, order your columns by equality conditions first, then range/sort conditions, while generally favoring columns with higher selectivity at the beginning.

Preview of the Next Lesson:
Indexes are incredibly powerful, but they aren't free. They take up disk space and, critically, they add overhead to write operations (INSERT, UPDATE, DELETE). In our next lesson, we will explore these trade-offs, helping you understand not just how to create an index, but also when and when not to.