Indexing in MySQL

Relational databases, adept at managing vast amounts of data, utilize Structured Query Language (SQL) for efficient data retrieval. Identifying specific rows becomes increasingly challenging as databases grow, akin to finding a needle in a haystack. Indexing is pivotal in enhancing search performance in MySQL, especially in large databases.

An index in MySQL is a sophisticated data structure created using one or more columns that facilitates rapid data retrieval and efficient record access. The CREATE INDEX statement in MySQL allows the formation of indexes. This organization is crucial for speeding up SELECT queries, while INSERT and UPDATE operations may require more time due to the additional steps of updating index values.

Indexes are invisible to users but are integral to the database engine, enabling it to swiftly locate rows irrespective of the table's size. This tutorial delves into the nuances of MySQL indexing, highlighting its creation and impact on query performance.

Syntax

Using the CREATE INDEX statement, one can generate indexes in MySQL. The syntax for constructing an index involves the following:

Here, indexname is the name of the index you want to create, tablename is the name of the table you want to index, and col1, col2, etc. are the names of the columns you want to include in the index.

Prerequisites

To use indexes, it's necessary to have a computer equipped with a relational database management system (RDBMS) that runs on SQL. The instructions and examples outlined in this article were verified using the following environment:

• You should have an Ubuntu 20.04 server running, and you have configured a non-root user with administrative permissions, as well as a firewall using UFW.
• MySQL must be installed and secured on the server.
• A fundamental understanding of executing SELECT queries to retrieve data from the database is required.

MySQL Index Syntax

Simple and Unique Index

It's possible to generate a unique index on a table, which means that two rows cannot possess the same index value. It's possible to use one or multiple columns to create an index. The following is the syntax for creating a unique index on a table:

ALTER Command to Add and Drop INDEX

A table can have indexes added to it using four types of statements.

1.

The primary key is added by using this statement, now indexed values must be unique and can't be NULL.
2.

This statement generates an index where values must be distinct, except for NULL values, which can occur multiple times.
3.

This creates a non-unique index where any value can occur multiple times.
4.

This generates a particular FULLTEXT index intended for searching text.

ALTER Command to Add and Drop the PRIMARY KEY

In the same manner, you can also add a primary key, but it must be based on columns that are NOT NULL. The code block below illustrates how to add a primary key to an existing table by first altering the column to NOT NULL and then designating it as a primary key.

To drop a primary key, you can use the ALTER command in the following way:

MySQL CREATE INDEX example

The following SQL statement generates an index called "index_carname" on the "CarName" column within the "Cars" table:

To generate an index based on a combination of columns, enclose the column names in parentheses and separate them with commas, as shown below:

Displaying INDEX Information

To obtain information about an index in MySQL, the command SHOW INDEX can be used. Here's how you can use it:

How MySQL Uses Indexes

MySQL utilizes indexes to quickly retrieve rows with specific column values, saving time and effort. In the absence of an index, MySQL has to start from the first row and scan the entire table to find the required rows, resulting in higher costs, especially for larger tables.

However, if the table has an index for the columns, MySQL can efficiently identify the position to seek in the middle of the data file, without having to scan through all the data, resulting in a much faster operation than sequential row scanning. B-trees are the preferred data structure for storing most MySQL indexes, including PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT.

How to Choose the Best Indexes for MySQL Query Optimization

To select the best indexes, it is essential to identify which operations utilize indexes.

The following operations in MySQL use indexes:

• To retrieve rows based on the WHERE clause.
• To exclude rows from the search result. In the case of using multiple indexes, MySQL has to select the most selective index, which searches for the smallest possible set of rows.
• You can utilize the MIN() and MAX() functions to determine the lowest and highest values, correspondingly, of a particular column.
• To sort or group items in a table.

Simply review your queries and identify the columns that will be searched, then index those columns.

Advantages of MYSQL Indexes

MySQL indexes offer several advantages, including:

• Faster data retrieval: MySQL can retrieve data more efficiently with the help of indexes, which enable the database engine to locate specific rows in a table faster.
• Better query optimization: MySQL can optimize queries using advanced techniques like covering indexes and index merges, thanks to indexes. This optimization results in faster and more efficient processing of complex queries by the database engine.
• Improved performance: Creating indexes on commonly used columns can improve the performance of SELECT statements, which are used to retrieve data from a database. This enhancement results in faster and more efficient query processing, thereby improving the overall performance.
• Reduced disk I/O: MySQL can minimize the disk I/O required to retrieve data by utilizing indexes.

Disadvantages of MYSQL Indexes

• Slower data modification: Modifying data (inserting, updating, or deleting) requires updating the relevant indexes, which can decrease the speed of data modification operations and increase their overhead.
• Indexing too many columns: Creating an excessive number of indexes or indexing too many columns can adversely affect performance. This is because it can lead to slower data modification operations and increased overhead in maintaining the indexes.
• Inappropriate use of indexes: Improper usage of indexes, such as creating them on rarely used columns or creating overly complex indexes, can also harm performance. Therefore, it's crucial to thoroughly analyze database usage patterns and create indexes only when necessary.