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-optimised 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 hard to find during development and QA environments where limited amount of data is used. But in production nasty performance and stability issues arises 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 Optimising 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 remaining uses InnoDB. In such cases MySQL will show warning messages to inform user that on the failure of transaction tables using InnoDB will be reverted back 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 greatest evil and majority of performance issues arises 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 don’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 good amount of sample data, you will definitely face lot of performance issue in production. Any poorly return 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
On 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 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 analyse the queries that are executed on a table and index accordingly. Badly created indexes causes 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 every 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.
