Indexes enable the server to navigate quickly to a desired position in the table, but that's not all they're good for. B+Tree indexes, which are the most common type in MySQL, function by storing the data in sorted order, and MySQL can exploit that for queries with clauses such as ORDER BY and GROUP BY. Because the data is presorted, a B+Tree index also stores related values close together. Finally, the index actually stores a copy of the values, so some queries can be satisfied from the index alone. Three main benefits proceed from these properties:
- Indexes reduce the amount of data the server has to examine.
- Indexes help the server avoid sorting and temporary tables.
- Indexes turn random I/O into sequential I/O.
Is an Index the Best Solution?
An index isn't always the right tool. At a high level, indexes are most effective when they help the storage engine find rows without adding more work than they avoid. For very small tables, it is often more effective to simply read all the rows in the table. For medium to large tables, indexes can be very effective. For enormous tables, the overhead of indexing, as well as the work required to actually use the indexes, can start to add up. In such cases, we might need to choose a technique (such as MySQL Partitioning) that identifies groups of rows that are interesting to the query, instead of individual rows.