When it comes to handling complex queries, one concept stands above the rest: composite indexing. By combining multiple columns into a single index, composite indexing unlocks the potential for faster data retrieval and optimized query execution.

In this article, we delve into the power of composite indexes and explore why they have become indispensable tools for maximizing database efficiency. Discover how composite indexing can revolutionize your approach to handling intricate queries and supercharge your overall query performance.💪

Before diving into the details, let's consider a specific query: determining the dob of Steve Rogers. To facilitate our discussion, let's examine a table called Avengers with the following structure: 📋

Now, we want to find the DOB for an AVENGER with the last name 'ROGERS' and the first name 'STEVE' using the following query: 🕵️

SELECT DOB
FROM AVENGERS
WHERE LNAME = 'ROGERS'
  AND FNAME = 'STEVE';

To optimize this query, we can consider different indexing options. Let's explore some alternatives:🤔

• No indexes: Although the table has a primary key on the 'SEQ' column, it doesn't significantly benefit our query. Here's the EXPLAIN output for the query:

id: 1
select_type: SIMPLE
table: AVENGERS
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
selname SIMPLE
tanamesidents
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 44
Extra: Using where

As shown, a table scan is performed, which can be inefficient for larger tables.

• INDEX(FNAME), INDEX(LNAME): Novice users often attempt to create separate indexes on each column. However, MySQL usually utilizes only one index per query. In this case, it would consider the options of using either the 'FNAME' or 'LNAME' index. Let's assume it chooses the 'LNAME' index:

id: 1
select_type: SIMPLE
table: AVENGERS
type: ref
possible_keys: LNAME, FNAME
key: LNAME
key_len: 92
ref: const
rows: 2
Extra: Using where

The query performs a range scan on the 'LNAME' index, fetches the corresponding primary key values (17, 36), accesses the data using those keys, applies the remaining WHERE clause, and finally returns the desired result.

• Index Merge Intersect: An advanced approach involves utilizing both the 'FNAME' and 'LNAME' indexes simultaneously. MySQL can perform an "Intersect" operation to merge the results. However, the EXPLAIN output doesn't provide detailed information on the number of rows fetched from each index:

id: 1
select_type: SIMPLE
table: AVENGERS
type: index_merge
possible_keys: FNAME, LNAME
key: FNAME, LNAME
key_len: 92, 92
ref: NULL
rows: 1
Extra: Using intersect(FNAME, LNAME); Using where

This approach still involves accessing the data table to retrieve the desired result.

• INDEX(LNAME, FNAME): A more optimal solution is to create a "compound" or "composite" index that includes both the 'LNAME' and 'FNAME' columns:

ALTER TABLE AVENGERS
ADD INDEX compound(LNAME, FNAME);

The resulting EXPLAIN output shows significant improvement:

id: 1
select_type: SIMPLE
table: AVENGERS
type: ref
possible_keys: compound
key: compound
key_len: 184
ref: const, const
rows: 1
Extra: Using where

With the compound index, the query can directly access the desired row using the index, eliminating the need for additional data retrieval steps.

• Covering: INDEX(LNAME, FNAME, DOB): Another enhancement involves creating a "covering" index that includes all the columns required in the SELECT statement:

ALTER TABLE AVENGERS
ADD INDEX covering(LNAME, FNAME, DOB);

The EXPLAIN output for this query shows that the index covers all the required data:

id: 1
select_type: SIMPLE
table: AVENGERS
type: ref
possible_keys: covering
key: covering
key_len: 184
ref: const, const
rows: 1
Extra: Using where; Using index

The "data" BTree is not accessed at all, resulting in improved performance compared to the compound index.

These are some examples illustrating the impact of different indexing strategies. Keep in mind a few additional points: 📝

• The order of columns in the WHERE clause doesn't affect the query's execution.

• The order of columns in the index can significantly impact performance.

• Including extra fields in an index doesn't harm performance and can even provide benefits like a covering index.

• Redundancy in indexes (e.g., INDEX(a), INDEX(a,b)) may negatively impact INSERT operations and isn't usually helpful for SELECT queries.

• Using prefix indexing (e.g., INDEX(LNAME(5), FNAME(5))) rarely provides significant improvements.

In summary, it's crucial to understand the importance of compound indexes and their impact on query performance. By carefully designing indexes, you can significantly enhance the efficiency of your database operations.

Cheers! 🥂