Using Indexes in MySQL
Tags: mysql, wedev, sql, todayilearned
Published: 2022-09-13 20:23:22

In this article, I discuss about query optimization using database indexes.
I discuss about _deciding whether you should use database indexes on your tables_, _types of indexes in MySQL_, _how to list existing indexes on your tables_, _how to create indexes_ and _how to delete indexes_.

**Indexes** are used to _find rows with specific column value(s) faster_.

Indexes in MySQL can be created on a **single column**:

```sql
CREATE INDEX <indexname> ON <tablename> (column1);
```

**or multiple columns**(composite index):

```sql
CREATE INDEX <indexname> ON <tablename> (column1, column2, ...);
```

An index may consist of up to 16 columns.

# Deciding Which Indexes to Create

Before you create indexes for a database table, consider how you will use the table. The two most common operations on a table are to:

- Insert, update, and delete records
- Retrieve records

If you most often **insert**, **update**, and **delete** records, then _the fewer indexes associated with the table, the better the performance_. This is because when querying the database, the driver must maintain _the indexes_ and also _the database tables_, thus slowing down the performance of query operations( inserts, updates, and deletes ).

If you most often _retrieve records_, you must look further to define the criteria for retrieving records and create indexes to improve the performance of these retrievals. Assume you have an employee database table and you will retrieve records based on employee name, department, or hire date. You would create three indexes-one on the DEPT field, one on the HIRE_DATE field, and one on the LAST_NAME field. Or perhaps, for the retrievals based on the name field, you would want an index created on multiple columns(multiple-column indexes) that concatenates the LAST_NAME and the FIRST_NAME fields (see "Indexing Multiple Fields" for details).

# Managing Indexes

## Showing Indexes

Tables can have multiple indexes. The query to list the existing indexes on a table is:

```sql
SHOW INDEX FROM <tablename>;
```

## Creating Indexes

In MySQL, we have three different methods for creating indexes:

**At the time of table creation**

- Using keyword `CREATE TABLE` to create a table
  ```sql
  CREATE TABLE <tablename> (
  ID int,
  LName varchar(255),
  FName varchar(255),
  DOB varchar(255),
  LOC varchar(255),
  INDEX ( ID )
  );
  ```

**After table has been created**

- Using keyword `CREATE INDEX`.
  ```sql
  CREATE INDEX <indexname> ON <tablename> (column1, column2, ...);
  ```
- Using keyword `ALTER TABLE`.
  ```sql
  ALTER TABLE <tablename> ADD INDEX (column1, column2, ...);
  ```

### Types of Indexes

**note**: _`KEY` is a synonym for `INDEX`_

1. Unique Index
   A **UNIQUE index** creates a constraint such that all values in the index **must be distinct**. An error occurs if you try to add a new record with column value matching existing column value.<br>
   A UNIQUE index _permits multiple NULL values for columns_ that can contain NULL.<br>
   You can create more than one UNIQUE index per table.

   **how it's created**:

   ```sql
   CREATE UNIQUE INDEX <indexname> ON <tablename>(column1, column2, ...);
   ```

   **or**

   ```sql
   CREATE TABLE <tablename> (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   column4 datatype,
   column5 datatype,
   ... ,
   UNIQUE KEY (column1, column2, ...)
   );
   ```

   **note**: _when you create a unique constraint, MySQL creates associated unique index behind scenes_.

   **or**

   ```sql
   ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> UNIQUE KEY(column_1, column_2, ...);
   ```

1. Primary Key

   A **primary key** is a _column or a set of columns that uniquely identifies each row in the table_.
   It is a type of unique index that **does not** permit NULL values for columns.

   It has an associated index, for fast query performance.
   We can only have one primary key in a database table.

   If the primary key consists of multiple columns, the combination of values in these columns must be unique.

   **how it's created**:

   If the primary key has one column:

   ```sql
   CREATE TABLE table_name(
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype,
    ...
   );
   ```

   If the primary has multiple columns:

   ```sql
   CREATE TABLE <tablename>(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...,
    PRIMARY KEY(column1, column2, ...)
   );
   ```

   **or**

   ```sql
   ALTER TABLE <tablename> ADD PRIMARY KEY(column1, column2, ...);
   ```

1. Simple index

   Also known as _Regular or Normal Index_.
   For the simple index, the specified column values **do not require to be unique** and as well as can be NULL also. They are supplemented basically to aid the database searching process for records quicker.

   **how it's created**:

   ```sql
   CREATE INDEX <indexname> ON <tablename>(column1, column2, ...);
   ```

   **OR**

   ```sql
   CREATE TABLE <tablename>(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...,
    INDEX(column1, column2, ...)
   );
   ```

   **OR**

   ```sql
   ALTER TABLE <tablename> ADD INDEX(column1, column2, ...);
   ```

1. Full-Text Index

   FULLTEXT index is used for full-text searches. This type of index is supported in columns with datatypes of `CHAR`, `VARCHAR` or `TEXT`. Only the InnoDB and MyISAM storage engines support FULLTEXT indexes.

   Occasionally, suppose you need to find the blob of text which includes a certain word or combination of words, or it can even be a substring found within the higher block text, then this full-text index is better applicable here. It is broadly implemented in search engines and e-commerce.

   **how it's created**:

   ```sql
   CREATE FULLTEXT INDEX <indexname> ON <tablename>(column1, column2, ...)
   ```

   **OR**

   ```sql
   CREATE TABLE <tablename>(
       column1,
       column2,
       column3,
       ...,
       FULLTEXT (column1, column2, ...)
   );
   ```

   **OR**

   ```sql
   ALTER TABLE <tablename> ADD FULLTEXT(column1, column2, ...)
   ```

# Deleting Indexes

While managing indexes, you may find it necessary to remove some. Below is how:

```sql
DROP INDEX <indexname> ON <tablename>;
```