What-is-indexing-in-MySQL

What is indexing in MySQL?

by
Share

In this article, we will discuss in detail about indexing in MySQL.

What is Indexing?

In order to fetch data with specific column values from the database quickly, indexes are used.

To easily understand what index is, we can take the example of any textbook. At the very end of the textbook, there is an index where the name of a topic and its page number are given alphabetically. By looking at that page number, we can easily reach that topic of the textbook. In this case, we don’t have to look for the whole book. This way we can save a lot of time.

Indexing works somewhat similarly in the case of MySQL. If the index is not present, MySQL will have to go through the entire table in order to fetch the specified row. The larger the table, the more time it will take to fetch the data.

MySQL index types

Below are the most used index types in MySQL mainly based on the purpose.

Primary key: Primary key is used to uniquely identify a record in a table. Primary key should be unique and not null. A table can have at most 1 primary key.
Unique key: Unique key also uniquely identifies a record in a table. Unique key should be unique but may contain a null value. A table can have multiple unique keys.
Full-text: Full-text index allows us full-text search functionality. This index can only be used in InnoDB or MyISAM tables. This is applicable only for CHAR, VARCHAR, or TEXT columns. Full-text searching is performed using MATCH() AGAINST() syntax where MATCH() takes a comma-separated list of the column names to be searched and AGAINST takes a string to search for.
Index: Apart from the three indexes mentioned above, we can also create indexes for any column of a table. Its value need not be unique and can be null. A table can have multiple indexes. We can create an index on one column (simple index) and multiple columns (composite index).
Spatial: This index can only be used in InnoDB or MyISAM tables. This is applicable only for GEOMETRY type columns. Spatial index should be not null.

MySQL index storage

Primary key, unique key, index, and full-text are stored in B-trees.
Spatial index is stored in R-trees.
Memory tables by default use hash index.

When indexing is important?

Indexing on columns is important when:

  • Using where clause on that columns
  • Using MAX(), MIN() on that columns
  • Using ORDER BY, GROUP by on those columns
  • Using those columns in MySQL JOIN

When indexing is less important?

Indexing on columns is less important when:

  • The table is too small
  • The table is large and the query returns most or all of the rows

# One thing we should keep in mind is that it takes more time to update the table with indexes than it does to update a table without them because they also need updates. So, we should create indexes only on those columns that will be used frequently for searching.

How to create index in MySQL?

Create index when creating a table

We can add index when creating a table.

We will now create the users table which will contain the fields id, email, display_name, first_name, last_name, description, and location.
We will create primary key on id column, unique key on email, simple index on display_name, composite index on first_name and last_name, full-text index on description, and spatial index on location while creating the table. There is no need to create all the index tables at creation time. We can create one or more indexes after creating the table. I will add all these indexes through an example for the convenience of understanding here.

We can use the SQL syntax below to create indexes when the users table is created:

CREATE TABLE users (
    id int NOT NULL,
    email varchar(255) NOT NULL,
    display_name varchar(255) NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255),
    description text,
    location GEOMETRY NOT NULL SRID 4326,
    PRIMARY KEY (id),
    UNIQUE (email),
    INDEX (display_name),
    INDEX (first_name, last_name),
    FULLTEXT (description),
    SPATIAL INDEX(location)
);

Create index on an existing table

If we had not added any index while creating the table above, that is if we had used the following syntax:

CREATE TABLE users (
    id int NOT NULL,
    email varchar(255) NOT NULL,
    display_name varchar(255) NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255),
    description text,
    location GEOMETRY NOT NULL SRID 4326
);

Even then we could add all these indexes and how we will know now.

We can create an index on the existing table by using the CREATE INDEX syntax in MySQL.

Create primary key

A primary key can be added to an existing table using the following SQL syntax. We will create primary key on id column.

ALTER TABLE users ADD PRIMARY KEY (id);
Create unique key

A unique key can be added to an existing table using any of the following SQL syntaxes. We will create unique key on email column.

ALTER TABLE users ADD UNIQUE (email);

CREATE UNIQUE INDEX idx_unique_email ON users (email);
Create full-text index

A full-text index can be added to an existing table using the following SQL syntax. We will create full-text index on description column.

ALTER TABLE users ADD FULLTEXT (description);
Create index on single column (simple index)

We can use the following syntax to create the index on display_name column in the users table.

CREATE INDEX idx_display_name ON users (display_name);
Create index on multiple columns (composite index)

The users table has first_name and last_name columns, and the following syntax can be used to create an index on their combination.

CREATE INDEX idx_fullname ON users (first_name, last_name);
Create spatial index

We can use any of the following syntaxes to create the spatial index on location column of GEOMETRY type in the users table.

ALTER TABLE users ADD SPATIAL INDEX(location);

CREATE SPATIAL INDEX idx_spatial ON users (location);

How to show index in MySQL?

SHOW INDEX or SHOW INDEXES SQL syntax is used to show all indexes from a table.

SHOW INDEXES FROM users\G;

Here \G is not required but we used it to see the output better. The output is:

*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 0
     Key_name: email
 Seq_in_index: 1
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: users
   Non_unique: 1
     Key_name: display_name
 Seq_in_index: 1
  Column_name: display_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 4. row ***************************
        Table: users
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 1
  Column_name: first_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 5. row ***************************
        Table: users
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 2
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 6. row ***************************
        Table: users
   Non_unique: 1
     Key_name: location
 Seq_in_index: 1
  Column_name: location
    Collation: A
  Cardinality: 0
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 7. row ***************************
        Table: users
   Non_unique: 1
     Key_name: description
 Seq_in_index: 1
  Column_name: description
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

How to delete index in MySQL?

The DROP INDEX syntax is used to delete an index in a table. If we want to delete the index idx_name created above we can use the syntax below.

ALTER TABLE products DROP INDEX idx_name;

We can learn more about indexing at: https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

2 thoughts on “What is indexing in MySQL?”

  1. Pingback: Webtechbased
  2. Pingback: Webtechbased

Comments are closed.