This article discusses indexes and how you can use them to improve MySQL database query performance.
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:
If you experience any of these issues, you should analyze your database queries and consider adding indexes.
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:
mysql -u username -p database
Type the following SQL command:
EXPLAIN SELECT * FROM table_name WHERE conditions \G
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:
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.
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:
mysql -u username -p database
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:
ALTER TABLE table_name ADD INDEX index_name (table_column);
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:
ALTER TABLE table_name DROP INDEX index_name;
To view all of the indexes for a table, type the following SQL command. Replace table_name with the name of the table:
SHOW INDEX FROM table_name \G
Subscribe to receive weekly cutting edge tips, strategies, and news you need to grow your web business.
No charge. Unsubscribe anytime.
Did you find this article helpful? Then you'll love our support. Experience the A2 Hosting difference today and get a pre-secured, pre-optimized website. Check out our web hosting plans today.
We use cookies to personalize the website for you and to analyze the use of our website. You consent to this by clicking on "I consent" or by continuing your use of this website. Further information about cookies can be found in our Privacy Policy.