Indexes (1/19)
What is an index in MySQL and why is it used?
    Understanding Indexes in MySQL and Why They Are Used

    An index in MySQL is a data structure—typically a B-tree or hash—that improves the speed of data retrieval operations on a table. Indexes allow MySQL to locate rows efficiently without scanning the entire table, making queries significantly faster, especially on large datasets.

    Why Indexes Are Used
    • To speed up SELECT queries by reducing the number of scanned rows.
    • To improve JOIN performance by indexing columns used in join conditions.
    • To enforce constraints such as PRIMARY KEY and UNIQUE.
    • To accelerate ORDER BY and GROUP BY operations.
    • To efficiently filter rows in WHERE clauses.
    Common Types of Indexes in MySQL
    • PRIMARY KEY – A unique index that identifies each row in a table.
    • UNIQUE Index – Ensures all values in a column are unique.
    • INDEX (Normal Index) – Speeds up lookup based on a column.
    • FULLTEXT Index – Used for text search on large text fields.
    • SPATIAL Index – Used for geometry or GIS data types.
    When Indexes Improve Performance
    • When filtering large tables with WHERE clauses.
    • When tables frequently perform join operations.
    • When sorting large datasets with ORDER BY.
    • When aggregating data using GROUP BY.
    When Indexes May Hurt Performance
    • Too many indexes slow down INSERT, UPDATE, DELETE operations.
    • Large indexes consume significant memory and disk space.
    • Ineffective indexes (e.g., on low-cardinality columns) do not help query speed.
    • Using functions on indexed columns prevents index usage.

    In summary, indexes are essential for speeding up data retrieval in MySQL, especially on large tables. However, they should be created thoughtfully, as unnecessary indexes can slow down write operations and waste storage.