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
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