Indexing Fundamentals
Learn about the fundamentals of indexing in databases.
We'll cover the following...
We often face situations where our queries run slower than expected, especially when working with large tables. For example, in our OnlineStore database, suppose we want to quickly find all customers who are in the VIP tier. If the Customers table has millions of rows, scanning through every single row just to filter those customers would be very slow. This is the exact problem that indexing solves. In this lesson, we’ll explore how indexes act like a super-fast table of contents for our database, allowing us to find the information we need in a fraction of the time.
By the end of this lesson, we will be able to:
Understand what an index is and why it’s crucial for database performance.
Differentiate between various types of indexes, such as B-Tree, clustered, and non-clustered.
Learn how to create and manage indexes using SQL commands.
Recognize the important trade-offs of using indexes in our database design.
Index and its importance
When we run a SELECT query with a WHERE clause on a table that has no indexes, the database engine performs what is called a “full table scan”. This means it literally checks every single row in the table to see if it matches our criteria. For a small table with a few hundred records, this is perfectly fine. But for a table with millions of records, like the Orders table in a large online store, a full table scan can take a very long time and consume a lot of resources, leading to slow application performance and a poor user experience.
This is where indexing comes to the rescue. An index is a special ...