In this short article, you will learn how to view, add and drop indexes using MySql:
To view the indexes in a table, try the following syntax, where test_table is the table name:
View Index in MySql
SHOW INDEX FROM test_table;
Add Index in MySql
To add an index on a field, try the following syntax, where index name is indexed_id having the field name test_id :
ALTER TABLE test_table ADD INDEX indexed_id (test_id);
Drop Index in MySql
To drop or remove index on a field, try the following syntax:
ALTER TABLE test_table DROP INDEX indexed_id;
Please note that if you are running an online website with heavy traffic, and you want to modify any index, for which you first have to drop the previous index and then add it. Then it is highly recommended to add the index first, with another index name, and then drop the previous index. By doing this, you will be able to have the following benefits:
- If in case the drop index query runs successfully, and add index query fails, then the time between fixing that query might be very critical, as it will affect the performance of your website, and the users will have difficulty in accessing your website in that time.