A data structure that enhances the speed of operations in a table is called as a database index.
Each time a web application runs a database query wherein the database checks all the rows in your table for finding those that match with the request. When your database table grows, it is required to inspect increasing number of rows each time and this can decrease the performance of the database as well as your application.
This problem is resolved by MySQL indexes by taking data from a column of your table and storing it in a separate location alphabetical wise and this is called an index.
You can easily create indexes via phpMyAdmin in cPanel. Below is an example of such index:
Here’s an example table called “sample” with only two rows – “number” and “employee”. If a simple SQL query is run such as:
SELECT * FROM sample WHERE number = 4;
MySQL will go through all records and will return only the one with the number value set to 4.
Suppose there are thousands of entries for instance, it will run the query slowly. Here a unique field “number” is present. So, an index can be created for it. An internal register will be created by indexing and is saved in by the MySQL service. This can be done with the below query:
ALTER TABLE sample ADD INDEX (number);
After you set the index, next time when you want to find the information for employee number 4, the service will go to it directly with the help of index and will return the information in a faster way.
It is a very basic example. In terms of bigger databases, the difference in the loading time can be high. When your database is indexed, it can drastically decrease the loading time of your web applications.
You can use another query to increase the loading time of your database as below:
OPTIMIZE TABLE sample;
That’s it! You have now learned to optimize MySQL using indexes.
