Learn What are Indexes

SQL Optimization and Query Features

Swipe to show menu

Indexes are special data structures that help databases find and retrieve rows much faster than scanning the entire table. Think of an index like the index in a book: instead of reading every page to find a topic, you can look up the topic in the index and jump straight to the relevant pages. In SQL databases, indexes work similarly by allowing the database to quickly locate rows based on the values in one or more columns.

Suppose you have a products table with thousands of rows, and you often need to find products by their name. Without an index, the database must scan every row to check the name value. This process is called a full table scan and can be slow as the table grows. By creating an index on the name column, you give the database a shortcut to quickly find the rows matching a specific name.


              12345678
            
-- Drop the index if it already exists to avoid errors
DROP INDEX IF EXISTS idx_products_name;

-- Create an index on the name column of the products table
CREATE INDEX idx_products_name ON products(name);

-- Test: Query using the indexed column to retrieve a product by name
SELECT * FROM products WHERE name = 'Apple iPhone 14';

After creating this index, any SELECT queries that filter products by the name column can use the index to find matching rows much faster. For example, a query like SELECT * FROM products WHERE name = 'Apple iPhone 14'; will benefit from the index. Instead of checking every row, the database uses the index to jump directly to rows where the name matches, reducing the time and resources needed for the query.

However, it's important to remember that indexes are most useful for columns that are frequently searched, filtered, or used in JOIN conditions. Creating too many indexes, or indexing columns that are rarely used in queries, can actually slow down other operations like INSERT, UPDATE, and DELETE, because the database must update the indexes as well as the table data.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 8

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 1. Chapter 8

What are Indexes

1. What is the primary purpose of an index?

2. On which columns should you consider creating indexes?

3. Can indexes slow down certain operations?