What is an Indexing?
An index is a type of data structure, similar to a lookup table, that is frequently implemented in MySQL as a B-tree. By sorting data values from one or more columns, this structure makes it possible for the database engine to locate particular rows considerably more quickly than it could by searching the entire table. MySQL constructs and preserves this ordered structure when you create an index on a column (or group of columns). Because the index structure needs to be updated each time data in the table changes, indexes can slow down INSERT, UPDATE, and DELETE operations even though they speed up SELECT queries.
Types of Indexes
Although MySQL offers a wide range of index types, basic B-tree indexes on InnoDB tables are essential and serve as the foundation for average workload performance.
- Primary Key Index: A primary key ought to be present in every table. The main key in InnoDB is a clustered index, which makes it unique. This indicates that the primary key values are used to determine the physical disk order of the data rows. There can only be one main key in a table. An index is automatically created by the main key constraint. Values in the primary key must be distinct and cannot be NULL.
- Secondary Indexes: The primary key is the only index that is considered a secondary index. The values of the indexed columns and a duplicate of the primary key value for the relevant row are included in secondary index entries in InnoDB. This enables MySQL to discover the entry in the secondary index and then locate the row in the primary key index.
- Unique Key Index: The creation of an index using a UNIQUE KEY guarantees that each value in the indexed column or columns is unique. Since NULL is regarded as distinct from all other values, including another NULL, a unique index on a column (or group of columns) that are not designated as NOT NULL will permit multiple NULL values, in contrast to a main key.
- Composite Index: A composite index is one that is constructed using several columns. The “leftmost prefix requirement” is the main reason why the order of columns in a composite index matters since it influences which queries may use the index efficiently.
Creating and Dropping Indexes
Using the ALTER TABLE or CREATE INDEX statements, indexes can be added later or built when the table is being defined.
- Creating an index during table creation:
- Adding an index to an existing table: A simple, unique, or composite index can be added to a table. When creating main or unique keys, the INDEX keyword is frequently unnecessary; however, for other indexes, it is necessary.
- Index dropping: You can drop an index by using its name (DROP PRIMARY KEY is used to drop the primary key).
Indexing: Applying Indexes for Maximum Leverage
Making indexes is only one aspect of indexing; another is strategically using them to get the most leverage. Indexing columns that enable MySQL to access the fewest amount of rows during query execution is the aim. This necessitates knowing the query as well as how MySQL “thinks” about index usage. Columns used in equality criteria (=) or IN clauses should be placed first when generating composite indexes. This is followed by a single column used in a range condition (BETWEEN, >, <), and finally columns used for GROUP BY or ORDER BY.
How Indexes are Used by MySQL
The query optimizer in MySQL chooses the optimal query execution strategy, including which indexes, if any, to utilize. The query execution plan is displayed by the EXPLAIN command. Indexes are used for a variety of operations as follows:
- Finding matching rows (WHERE): The most popular application of indexes is to find rows that match the WHERE clause’s requirements quickly. MySQL can use indexes for range searches (>, <, >=, <=, BETWEEN, LIKE with no leading wildcard), equality comparisons (=), and IN clauses. Type: range (range scan), type: const (constant value lookup, highly quick), or type: ref (equality lookup) may be displayed in the EXPLAIN output. A full table scan (type: ALL) may be used by MySQL if no appropriate index is identified for the WHERE clause.
- Grouping rows (GROUP BY): MySQL can group rows more effectively by using an index on the columns used in a GROUP BY clause. This is because the index already stores the data in a sorted manner. In some cases, this can eliminate the requirement for interim filesorts or tables. In the Extra field, the EXPLAIN output may display Using index or Using index for group-by. For this optimization to work, the GROUP BY columns must compose the index’s leftmost prefix.
- Sorting rows (ORDER BY): Without executing a separate sorting step (filesort), MySQL can provide results in sorted order thanks to indexes that can meet the ordering requirements of an ORDER BY clause. This is most effective when all of the columns in the ORDER BY have the same sorting direction (all ASC or all DESC, however MySQL 8.0 allows mixed directions with certain index types) and the ORDER BY columns constitute the leftmost prefix of an index. EXPLAIN will display Using filesort in the Extra field if an index cannot be used for sorting.
- Avoiding reading rows (Covering Indexes): A query is “covered” by an index if it contains all of the columns that were requested in the SELECT list and utilized in the WHERE clause (or other pertinent clauses like GROUP BY or ORDER BY). This prevents reading records. In this instance, MySQL does not need to visit the actual data rows in the primary key index in order to extract all of the required data directly from the index structure. Using index in the EXPLAIN output’s Extra field indicates this.
- Joining tables (Join Tables): For effective table joins, indexes are essential. For each row from the previous table in the join order, MySQL uses indexes on join columns (columns linked in the ON clause) to discover matching entries in the joined table rapidly. Instead of searching the full connected table for every row from the first table, it is much faster to perform an index lookup on the join column. The key used and the access type (such as ref or eq_ref) will be displayed in the EXPLAIN output for the joined table, while the column from the previous table that was used for the lookup will be indicated in the ref field.
In conclusion, obtaining high database performance requires a grasp of index types, how to construct and manage them, and most importantly how MySQL uses them for different query operations.