How to Rebuild Index in MySQL

1. MySQL Rebuild Index
2. Rebuild INDEXES Using REINDEX
3. Rebuild INDEXES Using the REPAIR TABLE Method
4. Rebuild INDEXES Using ALTER TABLE Method With InnoDB

This tutorial demonstrates how to rebuild indexes in MySQL.

MySQL Rebuild Index

MySQL handles data types and characters so that there can be a change in indexes or tables of MySQL. That is why sometimes we need to rebuild the indexes in MySQL.

There are a few methods to rebuild the indexes, and a few of them will repair or rebuild the whole database or the table where only the REINDEX is a data structure in MySQL that can be used to rebuild the indexes only.

We can use the REINDEX to rebuild the indexes for one or multiple columns, making the table perform better and accessing the data quickly.

Whenever a database is corrupted or needs a repair, then there will be a need to use the REINDEX to rebuild the indexes or repair the database or the table.

Most of the indexes of MySQL, including the PRIMARY KEY, UNIQUE, INDEX, FULLTEXT & REINDEX, are stored in the B-trees, which are self-balancing data structure trees to store the data in a particular manner.

Before rebuilding the indexes using the REINDEX or rebuilding the databases or tables, we first need to create a database with tables and indexes. This tutorial demonstrates different methods used to rebuild the indexes in MySQL.

Create Table and Indexes

Now let’s try an example to rebuild the indexes using the REINDEX, but before rebuilding the index, we first have to create the indexes so let’s start with that.

1. First, the syntax to create indexes is:

   CREATE INDEX [IndexName] ON [TableName] ([ColumnName]);
   

2. Now, let’s create a table

   create table Employee(id integer, name varchar(100), position varchar(100));
   insert into Employee(id, name, position) values(1, "Sheeraz", "SeniorDeveloper");
   insert into Employee(id, name, position) values(2, "John", "SeniorDeveloper");
   insert into Employee(id, name, position) values(3, "Jack", "JuniorDeveloper");
   select * from Employee;
   

   The code above will create a table with three rows and three columns. See the output:

   id	name	position
   1	Sheeraz	SeniorDeveloper
   2	John	SeniorDeveloper
   3	Jack	JuniorDeveloper
   

3. For position, there is more than one SeniorDeveloper. We can select the data according to the position and also explain that.

   See the example:

   SELECT id, name FROM Employee WHERE Position= "SeniorDeveloper";
   
   EXPLAIN SELECT id, name FROM Employee WHERE Position= "SeniorDeveloper";
   

   The first query will select the data where the position is SeniorDeveloper, and the second will explain how MySQL performs that. See the output:

   id	name
   1	Sheeraz
   2	John
   
   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	Employee	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where
   

4. Now, as we can see, there is more than one senior developer in the table, so we create an index for that column and then explain the above query to see the difference. See the example:

   CREATE INDEX position ON Employee(Position);
   
   EXPLAIN SELECT id, name FROM Employee WHERE Position= "SeniorDeveloper";
   

   After creating the index now, the output for the explain query is:

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	Employee	NULL	ref	position	position	403	const	1	100.00	NULL
   

   As we can see, the type, possible_keys, is changed to the position after creating the index position; a key is also assigned to it. We can also show the indexes by the following query:

   SHOW INDEXES FROM Employee;
   

   The output for this query is:

   Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
   Employee	1	position	1	position	A	NULL	NULL	NULL	YES	BTREE			YES	NULL
   

Rebuild INDEXES Using REINDEX

Now, if, in any case, after creating the indexes, the database or tables are corrupted, we can rebuild the indexes using the REINDEX, which will help us to reduce the memory and increase the IO efficiency.

The syntaxes for the REINDEX are:

# To REINDEX the whole database
REINDEX DATABASE [DatabaseName];
# To REINDEX the Table
REINDEX TABLE [TableName];
# To REINDEX the Particular column
REINDEX TABLE [TableName]([ColumnName);
# To REINDEX the INDEX
REINDEX INDEX [Index_Name];

Here, we can use REINDEX or OPTIMIZE to rebuild the corrupted indexes. See the example:

REINDEX TABLE Employee(position);

SHOW INDEXES FROM Employee;

Or:

OPTIMIZE TABLE Employee;

SHOW INDEXES FROM Employee;

Both of the above queries will rebuild the indexes for the table.

Output:

Table	Op	Msg_type	Msg_text
test.Employee	optimize	status	OK
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
Employee	1	position	1	position	A	1	NULL	NULL	YES	BTREE			YES	NULL

Rebuild INDEXES Using the REPAIR TABLE Method

Other than REINDEX, we can also repair the whole table, which will also rebuild the indexes in the table. There is a simple query to perform that.

REPAIR TABLE Employee;

SHOW INDEXES FROM Employee;

The above code will repair the table, rebuild the indexes, and then show the info of indexes. See the output:

Table	Op	Msg_type	Msg_text
test.Employee	repair	status	OK
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
Employee	1	position	1	position	A	1	NULL	NULL	YES	BTREE			YES	NULL

Rebuild INDEXES Using ALTER TABLE Method With InnoDB

We can also use the ALTER TABLE query to rebuild the corrupted table and indexes using the InnoDB engine. Here is the simple query to perform that.

ALTER TABLE Employee ENGINE = InnoDB;

SHOW INDEXES FROM Employee;

The code will use ALTER to rebuild the table and indexes using the InnoDB engine.

Output:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
Employee	1	position	1	position	A	2	NULL	NULL	YES	BTREE			YES	NULL