How to use indexes to improve MySQL query performance

Database performance and indexes

Database indexes in MySQL enable you to accelerate the performance of SELECT query statements. For small tables, an index does not help much. However, if you have tables with a large amount of data, indexes can dramatically improve performance.

The following behaviors are all indications that tables may benefit from adding indexes:

• Your site loads very slowly or not at all.
• An application is unable to connect to its database.
• Database queries are backed up.

If you experience any of these issues, you should analyze your database queries and consider adding indexes.

If you have a managed VPS or managed dedicated server, an increased sort buffer size may also help in addition to adding indexes. Please open a ticket on the Customer Portal at Hostvento Hosting to discuss this option with our Guru Crew.

Analyzing database queries

To determine which tables may benefit from using indexes, you must analyze your database queries. The EXPLAIN SELECT statement helps you do this. To analyze database queries, follow these steps:

1. Log in to your account using SSH.
2. At the command prompt, type the following command, replacing username with your Hostvento Hosting account username, and database with the name of the database:Copymysql -u username -p database
3. At the Enter Password prompt, type your password. The mysql> prompt appears.
4. Type the following SQL command:
   CopyEXPLAIN SELECT * FROM table_name WHERE conditions \G If you write your own application code, then you already know the SELECT statements you use to retrieve data. If you are using a third-party application, such as WordPress or PrestaShop, you may need to examine the source code or database logs to determine the exact SELECT statements that are used to retrieve data.
5. Output from the EXPLAIN SELECT statement shows how the MySQL query optimizer will execute the query. For example, consider the following output:
   mysql> EXPLAIN SELECT title FROM employees WHERE lastname LIKE ‘T%’ \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 142 Extra: Using where 1 row in set (0.00 sec) In this example output:
   • The possible_keys and key values are both NULL, which indicates that MySQL does not have an index it can use for this query.
   • The rows value indicates that MySQL will read 142 rows for this query. If there are 142 total rows in the table, this means MySQL must examine every row to generate the result set. This could take quite some time for a large table.
   On the other hand, if we were to create an index called index_name for the lastname column, MySQL might generate the following output for the same query:mysql> EXPLAIN SELECT title FROM employees WHERE lastname LIKE ‘T%’ \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: range possible_keys: index_name key: index_name key_len: 22 ref: NULL rows: 17 Extra: Using where; Using index 1 row in set (0.00 sec) As you can see, the possible_keys and key values indicate that MySQL has found an index it can use to optimize the query. Additionally, MySQL will read only 17 rows to generate the result set, instead of all 142 rows. (This means the table has 17 rows where the last name begins with “T”.) Lastly, the Extra value also indicates that MySQL will use an index for the query.

Adding, removing, and viewing indexes in a table

After you analyze your database queries and determine where the performance bottlenecks are, you are ready to add indexes. To do this, follow these steps:

1. Log in to your account using SSH.
2. At the command prompt, type the following command, replacing username with your Hostvento Hosting account username, and database with the name of the database:Copymysql -u username -p database
3. At the Enter Password prompt, type your password. The mysql> prompt appears.
4. To add an index to a table, type the following SQL command. Replace table_name with the name of the table, index_name with the name of the new index (which can be anything you want), and table_column with the name of the table column for which you want to add the index:
   CopyALTER TABLE table_name ADD INDEX index_name (table_column); Although indexes can improve performance, indexes can also negatively impact performance if there are too many of them. This is because the more indexes a table has, the more work MySQL must do to keep them updated. The trick is to find the right balance between enough indexes to improve performance, but not so many that they negatively impact performance.
5. To remove an index from a table, type the following SQL command. Replace table_name with the name of the table, and replace index_name with the name of the index that you want to delete:
   CopyALTER TABLE table_name DROP INDEX index_name;
6. To view all of the indexes for a table, type the following SQL command. Replace table_name with the name of the table:
   CopySHOW INDEX FROM table_name \G After you create an index, you should use the EXPLAIN SELECT statement again to see how queries are affected. Additionally, you should continue to monitor database performance to see if there are improvements. You may have to run a series of tests to find the optimal number of indexes for your database.