Optimizing MySQL performance – queries
MySQL performance optimizations can be divided into two parts. One is query optimization and the other is MySQL server configuration. To get optimum results, you have to work on both of these parts. Without proper configuration, queries will not provide consistent performance; on the other hand, without proper queries and a database structure, queries may take much longer to produce results.
In this recipe, we will learn how to evaluate query performance, set indexes, and identify the optimum database structure for our data.
Getting ready
You will need access to an admin account on the MySQL server.
You will need a large dataset to test queries. Various tools are available to generate test data. I will be using test data available at https://github.com/datacharmer/test_db.
How to do it…
Follow these steps to optimize MySQL performance:
The first and most basic thing is to identify key columns and add indexes to them:
mysql> alter table salaries add index...
