What is the role of an index in a database and why is it necessary?

An index in a database is a special data structure that speeds up the search and retrieval of records based on certain table fields. Without an index, the DBMS has to scan all rows of the table (full scan), which slows down the operation.

Indexes are needed for:

• Fast data search by keys (e.g., by ID, name, date).
• Accelerating sorting and grouping operations.
• Ensuring the uniqueness of values (unique indexes).

Example: if you have a products table with millions of records and you often search for products by name, creating an index on the "name" field will allow you to find the needed records much faster.

In Python, using SQLite, an example of creating an index:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('CREATE INDEX idx_product_name ON products(name)')
conn.commit()
conn.close()