If I truncate a table, should I also add indexes?

If you truncate a table, you do not need to add indexes because table is recreated after truncating a table and indexes get added automatically.

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> FirstName varchar(20),
   -> LastName varchar(20)
   -> );
Query OK, 0 rows affected (0.65 sec)

Following is the query to create an index −

mysql> create index Index_firstName_LastName on DemoTable(FirstName,LastName);
Query OK, 0 rows affected (1.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

Insert some records in the table using insert command −

mysql> insert into DemoTable(FirstName,LastName) values('John','Smith');
Query OK, 1 row affected (0.47 sec

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+----+-----------+----------+
| Id | FirstName | LastName |
+----+-----------+----------+
|  1 | John      | Smith    |
+----+-----------+----------+
1 row in set (0.00 sec)

Before truncating the table, you need to check the table status −

mysql> show create table DemoTable;

Output

This will produce the following output −

+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create
Table|
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable    | CREATE TABLE `DemoTable` (`Id` int(11) NOT NULL AUTO_INCREMENT,`FirstName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,`LastName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,PRIMARY KEY (`Id`),KEY `Index_firstName_LastName` (`FirstName`,`LastName`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now truncate the table −

mysql> truncate table DemoTable;
Query OK, 0 rows affected (2.01 sec)

Now check the table status once again. You do not need to add indexes −

mysql> show create table DemoTable;

Output

This will produce the following output −

+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create
Table
|
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable | CREATE TABLE `DemoTable` (`Id` int(11) NOT NULL AUTO_INCREMENT,`FirstName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,`LastName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`Id`),KEY `Index_firstName_LastName` (`FirstName`,`LastName`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)