MISSION 116

Introduction to Indexing

In previous lessons, we've learned how to query, modify, and create tables in a database. In this lesson, we'll explore how queries are executed in SQLite. We’ll also learn how to create and use indexes for better performance. As our data becomes larger and our queries more complex, it's important to be able to tweak the queries we write and optimize a database's schema to ensure that we're getting results back quickly.

The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined. While indexes speed up queries, they come at the cost of space as each index needs to be stored in a database file. Because of this, it's recommended to only create an index when you find yourself querying a specific column frequently.

To explore database performance, we'll work with `factbook.db`, a SQLite database that contains information about each country in the world. We'll be working with the `facts` table in the database.

Objectives

  • Learn how the EXPLAIN query plan works.
  • Learn how to create a table index in SQLite.

Mission Outline

1. Introduction
2. Query planner
3. Explain query plan
4. Data representation
5. Time complexity
6. Search and rowid
7. Indexing
8. Create an index
9. All together now
10. Next steps
11. 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