Indexing in database / how to optimize mysql table
Indexing is a data structure technique, which is used to efficiently retrieve a records from the database files, based on some attributes on which the indexing as been done. For example, indexing in database is also similar to what we seen in books.
Indexing can be defined based on it attributes. Indexing can be of following types.Such as
- Primary Index – It is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of a relation
- Secondary Index – It may be generated from a field which is a candidate key and has a unique value in every record or a non_key duplicate values.
- Clustering Index – It is defined on an ordered data file.The data file is ordered on a non_key field.
How to optimise the MYSQL database
The most important condition to improve the performance of MYSQL database is indexing. Indexing is an internal MYSQL feature that allows faster gathering of data.
For example, create a table called “employee” with only two rows – name and number. Run a simple query.
SELECT * FROM employee WHERE number=2;
After executing the SQL query, it checks and retrieve the records only that has its value set to 2.
But if you have several bulk of entries in a table, then this query will be too slow. In this situation, we have a unique_field ‘number’. So we can create an index for it. Also indexing will create a internal registry, that will be saved by the MYSQL service. It can be only possible with the following query.
ALTER TABLE employee ADD INDEX (number);
Once you set the index to the field, if you want to get the information of an employee number= 2 then the service will go start away to it using the index and will return on the information as much as faster.
For a bigger database, the difference is loading time can be significant. Indexing the database can rapidly decrease the loading time of your web application.
The following query is used to increase the loading speed of your database.
OPTIMIZE TABLE employee;
Note: Using SSH(Secure Shell), you can also optimize the MYSQL database. Use the following command,
mysqlcheck -orp -u user_name database_name
where,
-o ->stands for optimize
-r ->stands for repair
-p -> when this option is used with the command, then you will be prompted to enter the password of your database username
-u ->username assigned to the database.