Use indexes to reduce the overhead on MySQL server
In MySQL database, indexes are used to optimize the database server by reducing the cost for searching data. Take it as an example of index page you see at the end of text books. It contains the page number of important words, so you do not have to check each page of the book to find that word. You just get the page number from index page, and directly lands on that specific page.
Same is the case with indexes in MySQL database. Indexes are applied on column level, so you can specify which values should be indexed. Generally, we apply indexes on those columns whose values are searched most often. For example, in user’s table, one might search for user by his email address. So we can apply the index on email address.
In this tutorial, we are using sample database called classicmodels and the table where we will be working is named as orderdetails. As discussed earlier, indexes are applied on column level, so we will be using column named productCode for this purpose. So let’s get started.
Get all indexes on table
The basic query of getting all indexes applied on some specific table is:
SHOW INDEX FROM table_name;
It will return name of index, sequence number of when applied, column name, integrity and collation etc. The output will be similar as screenshot above.
Even if you haven’t applied any index on any column of that table, you will still see indexes already applied on your table’s primary key (if you have any). They will be applied at the time of creation of table.
You can try running the query on productCode column using EXPLAIN alias and you will see the number of records MySQL server has to go through, just to fetch specific records.
EXPLAIN SELECT * FROM orderDetails WHERE productCode = "S18_3232";
In our case, the query has returned 53 records without applying EXPLAIN clause. And after applying EXPLAIN clause, it will tell that it has searched in 2996 records. So it is an overhead on MySQL server that it has to search in 2996 records just to pull 53 records. So we will be applying index on this column and you will see how much the query will be optimized.
Add index on column
You can either apply index by using ALTER statement or by CREATE INDEX, but we recommend using ALTER since it is been used by other DDL operations too, so it will be easier for you to remember. Also, ALTER helps you when deleting an index from table. Deleting index from column is also discussed at the end of this tutorial.
ALTER TABLE table_name ADD INDEX index_name (column_name);
In this statement, index_name can be anything of your choice. But make sure that it does not have any space and that index name must not be applied on any column before.
Check performance of query
As discussed earlier, we will be applying index on column named productCode. So suppose we have applied the index using the above query, and now we want to see the performance of our query.
After applying the index, you can run again the query but put EXPLAIN clause before the query. So you will performance of query. You will see that the number of rows fetched will be equal (or almost equal) to the number of rows MySQL server has seached.
In our case, MySQL server returned 53 rows using the above query. And the number of rows it has to go through is also 53. Your number may differ based on the size of your table and the query that you are testing.
Also, you will see the column named filtered in result of EXPLAIN query. That tells the percentage of data it has filtered between number of searched rows and number of rows in result.
Delete indexes from column
This will be almost similar to the add index section. You just need to replace the “ADD” with “DROP” and execute the query. In this query, you do not have to specify the name of column, just specify the name of index and it will be done.
ALTER TABLE table_name DROP INDEX;
Try running the show index query again and you will see that specific index will be removed from that column.
