Knowledge Base
MilesWeb / MySQL

How to Optimize MySQL using Indexes?

Approx. read time : 3 min

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:

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:

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:

That’s it! You have now learned to optimize MySQL using indexes.

Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.
Need help? We’re always here for you.