What is a Database index?
A database index is a data structure (most commonly B-tree) that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table.
Example:
Suppose that we have a database table called Customer with three columns – customer_name, customer_age, and customer_address. Assume that the Customer table has thousands of rows.
Now if we want to run a query to find all the details of any Customer who are named ‘John’? So, we decide to run a simple query like this:
SELECT * FROM Customer
WHERE customer_Name = ‘John’
What would happen without an index?
When we run that query the database software would literally have to look at every single row in the Customer table to see if the customer_name for that row is ‘John’. And, because we want every row with the name ‘John’ inside it, we can not just stop looking once we find just one row with the name ‘John’, because there could be other rows with the name John. So, every row up until the last row must be searched – which means thousands of rows in this scenario will have to be examined by the database to find the rows with the name ‘John’. This is what is called a full table scan.
How do database indexes work?
The purpose 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. Like tables, indexes consist of rows and columns but store the data in a logically sorted manner to improve search performance. Think of it like a telephone book (a printed one). They are usually sorted last_name, first_name and potentially other criteria (e.g. zip code). This sorting makes it possible to find all entries for a specific last name quickly. If you know the first name too, you can even find the entries for the combination last name/first name very quickly.
