What is the purpose of an index?
To help the database find data faster by using shortcuts.
Indexes improve query performance by reducing the amount of data the database needs to scan.
What happens without an index?
The database must read every row (full table scan).
This can significantly slow down query performance, especially with large datasets.
Analogy for no index?
Searching for one paper in a messy shoebox.
This illustrates the inefficiency of searching through unorganized data.
What happens with an index?
The database jumps straight to the correct spot.
This allows for quicker data retrieval compared to scanning the entire table.
Analogy for having an index?
Using a tab in a cookbook.
This shows how an index helps locate information quickly.
What is a clustered index?
The table is physically sorted in that order.
This means that the data rows are stored in the same order as the index.
How many clustered indexes can a table have?
Only one.
This is because the data can only be sorted in one way.
What is a clustered index usually built on?
The primary key.
This ensures that the most unique identifier is used for sorting.
What is a clustered index good for?
Searching ranges (e.g., ID 1000–2000, last week’s orders).
This allows efficient retrieval of a range of records.
Baby version of clustered index?
The table itself is sorted.
This simplifies the concept for easier understanding.
What is a non-clustered index?
A separate list that points to the actual data.
This allows for multiple indexes on different columns.
Analogy for a non-clustered index?
A library card catalogue.
This illustrates how a non-clustered index directs you to the location of data.
How many non-clustered indexes can a table have?
Many.
This flexibility allows optimization for various queries.
What are non-clustered indexes good for?
Searching columns like Email, LastName, ProductCategory.
This enhances performance for queries involving these columns.
Baby version of non-clustered index?
A cheat sheet that points to the data.
This analogy helps simplify the concept.
Why does a table scan happen?
Because no index exists on the searched column.
This leads to slower query performance.
How do indexes improve speed?
They allow the database to jump directly to the needed rows.
This significantly reduces the time taken to retrieve data.
How much faster can indexes be?
Up to 100x faster.
This highlights the efficiency gained through indexing.
Example: finding CustomerID = 96 without an index?
Must check rows 1 → 96 one by one.
This illustrates the inefficiency of a full table scan.
Example: finding CustomerID = 96 with an index?
Goes straight to “96” in the sorted list.
This demonstrates the efficiency of using an index.
Baby version of why indexes are fast?
Skip the line and go straight to your name.
This analogy makes the concept relatable.
Why do INSERTs slow down with indexes?
The index must be updated too.
This adds overhead to write operations.
Why do UPDATEs slow down with indexes?
The database must keep the index in order.
This requires additional processing time.
Analogy for index maintenance?
Adding a new recipe to an alphabetised cookbook.
This illustrates the effort needed to maintain order.
