MySQL databases are widely used in production for its features and free nature. Like in every production servers, the maintenance of the databases decides an applications’ performance and stability. The main performance issue that appears in any application can be categorized into the below three categories, in the order of importance:
- Non-optimized Queries
- Bad MySQL server Configuration
- Insufficient Hardware
In this series of articles, we will be focusing on the rules for optimizing a query and how to write an optimized and efficient MySQL query. Remember you can use these rule on any databases (SQL Server, Oracle, Sybase, etc.) and only the commands, statements and jargon may differ between databases, but the concept remains the same.
Non-optimized queries are difficult to find during development and QA environments where a limited amount of data is used. But in production, nasty performance and stability issues arise when the server has huge data. Here are some examples of bad queries you can find in many applications:
- Queries that don’t use indexes
- Queries that use SELECT *
- Queries that search full-text fields
- Queries that are not properly limited
- Queries that use ORDER BY unnecessarily
- Queries that use COUNT(*)
Some common fallacies and problems and Rules for Optimizing Queries
Myth – 1
InnoDB is better than MyISAM for production database, as InnoDB provides full ACID compliance.
Fact
This is only true if the tables are taking part in transactions, and it is also true that InnoDB provides full ACID compliance, but that comes at a cost. While MyISAM offers speed but offers no support for transactions. But the entire statement is nullified if you use multiple tables in your transactions and some of them uses MyISAM and the remaining uses InnoDB. In such cases, MySQL will show warning messages to inform the user that on the failure of transaction tables using InnoDB will be reverted to original state, while tables using MyISAM will be in committed state.
Rule
Myth – 2
Queries employing full table scan or bad for DB performance.
Fact
This is true. If there is a full-table scan, poor performance will result. This is the greatest evil, and the majority of performance issues arise due to this. To avoid queries using full tables scan, create indexes for columns that are used in conditions. But remember, having too much indexes will affect the performance of insert statements. Also note that, in some scenarios, creating indexes doesn’t stop a full-table scan.
Rule
Myth – 3
Test / Sample Data is so important in tuning up database queries
Fact
This is true. If you have not tested your application with a good amount of sample data, you will definitely face a lot of performance issue in production. Any poorly returned query will appear to run superbly with an insignificant amount of data. So, always try to estimate the amount of real data in production and try to have similar amount of data in your development or testing server to test the actual performance of the query.
Rule
Myth – 4
Using COUNT(*) to find the total number of rows is faster and easier to use.
Fact
In Contrary to this belief, when you are using InnoDB, COUNT(*) statements actually cause performance problems. Moreover, COUNT(*) does not provide the actual number of rows in a table since, some transaction may be running at the time of execution. Also, a COUNT(*) statement will cause a full-table scan, thus contradicting the third rule for performance tuning. Yes, there may be some scenarios where we cannot avoid COUNT(*) statements. But, in the rest of the case, it is always better to find other alternatives to get to the result.
Rule
Myth- 5
Whenever you face performance issues during select statements, add indexes to tables
Fact
Do not add indexes as a solution to every performance problem you face. Always analyze the queries that are executed on a table and index accordingly. Badly created indexes cause serious overhead and leads to slow insertion and updates on a table.
Rule
Myth – 6
Using EXPLAIN is the best way to understand a query execution
Fact
It is true and always use EXPLAIN now and then. This is an excellent tool to see what may go wrong.
Rule
The above are six Myths, Facts and Rules in tuning up a query in MySQL database. In the next part of the series, we will cover how to write queries using these 6 rules.
