MySQL Interview Question: Suppose to create a composite index (a, b, c) If you query fields A and c, will this composite index be used?

Let’s create a lesson plan focused on MySQL, specifically on understanding the application of composite index

Thank you for reading this article. More Interview Questions here:
https://programmerscareer.com/software-interview-set/

Topic: 1.1 Introduction to Composite Indexes in MySQL

Composite indexes, also known as concatenated or multi-column indexes, are a form of database index that spans two or more columns in a database table. These columns are arranged in a specific order, and the composite index is directed by this column order.

In a simple analogy, consider a library. Single-field indexing is like sorting the books solely based on authors’ names. However, a composite index is more like sorting the books based on both authors’ names and book titles.

In MySQL, composite indexes play a fundamental role in increasing the efficiency of database operations. It’s important to understand that MySQL can use a composite index for queries that involve any of the columns in the index, but the index would be most efficient when you use the leftmost prefix of the index column list.

One key feature of MySQL composite indexes is “leftmost prefixing”. In a composite index, the column order matters. In a composite index (a, b, c), MySQL can use this index to speed up queries involving ‘a’ or ‘a and b’, but not ‘b and c’, because ‘b and c’ is not a leftmost prefix of the index column list.

So, composite indexes can improve query performance to a great extent, but their efficient use depends on how we are querying the data.

Topic: 1.2 Creating a Composite Index

Creating a composite index in MySQL is similar to creating a single-column index, with a slight change in syntax.

Here’s the basic syntax for creating a composite index:

CREATE INDEX index_name  
ON table_name(col1, col2, …);

• index_name is the name you want to give to the index.
• table_name is the name of the table you want to create the index on.
• (col1, col2, …) are the names of the columns you want to include in the index, in the order you want them to appear.

Let’s consider a practical example. Suppose there’s a sales table with ProductID, OrderDate, and Region columns. If you often perform queries that filter or sort by ProductID and OrderDate, a composite index will be beneficial. Here’s how you’d create it:

CREATE INDEX idx_sales  
ON sales(ProductID, OrderDate);

In the above example, idx_sales is a composite index on the sales table including ProductID and OrderDate. The order of columns matters because MySQL will use the index for queries that involve ProductID alone or both ProductID and OrderDate, but not OrderDate alone.

Remember that using composite indexes comes with trade-offs. While they can significantly speed up querying, they can also slow down write operations (like INSERT, UPDATE, DELETE), because MySQL must also update the indexes. It’s important to find the right balance to optimize your database performance.

Topic: 1.3 Querying A and C Fields

This topic tackles an important question: Will MySQL use a composite index (a, b, c) if you’re querying only fields A and C?

The answer largely depends on your SQL query and how you structured your index. If the columns being used in the WHERE clause are the first N consecutive columns of the index, then the index can be used. So if you’re querying on a composite (a, b, c) index with fields A and C but without field B, the index may not be as effective.

For example, consider this composite index:

CREATE INDEX comp_index  
ON table(a, b, c);

And suppose we have this query:

SELECT *  
FROM table  
WHERE a = 1 AND c = 3;

In this case, MySQL would only use the index on field a and would ignore the index on c, because c is not contiguous with a in the index leftmost prefix.

Alternatively, to make sure the index on fields a and c gets used, you could restructure your composite index as (a, c, b) or (c, a, b) and adjust your queries accordingly. The point here is that the fields in WHERE clause should align with the leftmost prefix of the index columns.

Keep in mind that it’s always a good idea to regularly analyze the performance of your queries and adjust indexes as necessary. MySQL’s EXPLAIN statement is a useful tool to understand how your queries interact with indexes.

In the next section, we will learn how to optimize the use of composite indexes in MySQL for better results.

Topic: 1.4 Composite Index Optimization

Optimizing composite indexes in MySQL can significantly improve your database queries’ efficiency and speed. Remember, an efficiently implemented index saves time, resources, and improves overall application performance.

Here are several key points to remember for the effective use of composite indexes:

1. Order of Columns: The order of columns in the composite index can make a significant difference. MySQL can efficiently use the index if the columns in your query align with the leftmost prefix of the index. If your WHERE clause uses several columns, you might obtain multiple indexes or a composite index — the choice between these options would rely on specific application requirements.
2. Index Cardinality: Index cardinality refers to the diversity of indexed values. Index columns with higher cardinality lead to fewer row scans and increased query performance. Hence, in a composite index, the column with the highest cardinality should ideally be placed first.
3. Equality vs. Range Conditions: In a composite index, MySQL can perform equality checks for all columns and a range check for the last column. If there’s a range condition in the middle of your WHERE clause, MySQL can’t use the index parts to the right of that range.
4. Over-Indexing: While indexes accelerate data retrieval, they slow down data modifications such as INSERT, UPDATE, and DELETE queries because each modification in indexed column data requires an update in the index structure. Ensure you’re not over-indexing your tables — every index should serve a purpose.
5. Use EXPLAIN: The EXPLAIN keyword in MySQL shows how the optimizer chooses indexes to execute the query. Regularly check your queries using EXPLAIN to understand how the optimizer interacts with your indexes.

Topic: 1.5 Review and Assessments

Over the course of our sessions, we’ve learned about Composite Indexes in MySQL, their structure, and their distinction from single field indexes. We studied the syntax and examples of creating a composite index. We then explored querying specific fields like A and C with a composite index. We then progressed to achieve a deeper understanding of optimizing our use of composite indexes in MySQL.

Key concepts we covered include:

1. Composite Indexes: They’re indexes that span multiple columns. This multi-column categorization can dramatically speed up query execution.
2. Creating Composite Indexes: We studied the syntax and walked through an example of creating a composite index.
3. Querying A and C Fields: We observed that MySQL can only utilize the portions of an index where the columns are referenced in the WHERE clause from left to right without skipping any columns.
4. Composite Index Optimization: We went through some essential tips and tricks for optimizing the use of composite indexes, including the order of columns, index cardinality, distinguishing between equality and range conditions, avoiding over-indexing and making use of the EXPLAIN keyword in MySQL.

It’s now time to assess your understanding of Composite Indexes.

Example Problem:

Let’s say you are running an online book store that has thousands of books. Your main books table in your MySQL database has the following columns: id, title, author, genre, publication_year and price. You realize that you frequently execute the following query:

SELECT *  
FROM books  
WHERE genre = 'Science Fiction' AND publication_year = 2018;

To optimize this query, you create the following composite index:

CREATE INDEX genre_year_index  
ON books (genre, publication_year);

This composite index should make your common query faster.

Now, it’s your turn!

Question: It’s the holiday season and your store is offering discounts on many books. You plan to send an email to all users who bought ‘Science Fiction’ books published after 2010. Write a SQL query to extract a list of user_ids from an orders table, which has the columns: order_id, user_id, book_id, order_date. Assume there is also a junction table named ‘orders_books’ with order_id, book_id.

---

to get the list of user_id from the orders table who bought ‘Science Fiction’ books published after 2010, we will use JOIN to combine information from the orders, orders_books and books tables.

Your SQL query would look something like this:

SELECT DISTINCT o.user_id  
FROM orders AS o  
JOIN orders_books AS ob ON o.order_id = ob.order_id  
JOIN books AS b ON ob.book_id = b.id  
WHERE b.genre = 'Science Fiction' AND b.publication_year > 2010;

This query checks the genre and publication_year of the books in each order and returns the user_id for those matching the criteria. The DISTINCT keyword is used to eliminate any duplicate user_id from the result.

Remember, understanding the structure of your data and how it is related is crucial when working with SQL and databases. Also, always assure you have the right indexes set for your queries.

中文文章: https://programmerscareer.com/zh-cn/mysql-interview19/
Author: Wesley Wei – Twitter Wesley Wei – Medium
Note: If you choose to repost or use this article, please cite the original source.