MISSION 117

Multi-column indexing

In the Introduction to Indexing lesson, we explored how to speed up `SELECT` queries that only filter one column by creating an index for that column.

In this lesson, we'll explore how to create indexes for speeding up queries that filter multiple columns. In addition, you'll learn concepts such as multi-column indexes, covering indexes, and query plans for multiple columns. Not only will you learn how to create multi-column indexes, you will learn why multi-column indexes are critical to know.

We'll also explore how to modify the queries we write to better take advantage of indexes. For example, if we create an index for the name column, we'll explore why the following query:

`SELECT name from facts WHERE name = 'India';`

will be faster than:

`SELECT * from facts WHERE name = 'India';`

We'll continue to work with `factbook.db`, a SQLite database that contains information about each country in the world. Recall that this database contains just the facts table and each row represents a single country. 

Objectives

  • Learn how to use multi-column indexing to speed up certain queries.
  • Learn how to understand a multi-column query plan.
  • Learn what a covering index is.

Mission Outline

1. Introduction
2. Query Plan
3. Query plan for multi-column queries
4. Explanation
5. Multi-column index
6. Creating a multi-column index
7. Covering index
8. Covering index for single column
9. Conclusion
10. Takeaways

sql-databases-advanced

Course Info:

Advanced

The median completion time for this course is 6.1 hours. View Details

This course requires a basic subscription. This course includes four missions and one installation tutorial.  It is the 13th course in the Data Analyst in Python path and Data Scientist in Python path.

START LEARNING FREE

Take a Look Inside