MySQL Query Tune-up And Performance Guide – Rules For Optimising Query – Part 1

Edward Ramamoorthy

13 years ago

Categories: PC & Linux

Tags: Mysql, Mysql Guide

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

Use InnoDB engine for tables taking part in transactions and MyISAM engine for tables that don’t take part in transaction for better performance.

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

Use Indexes for columns used in where conditions and try to avoid writing queries that cause full-table scan.

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

Always test your database queries with lot of sample data.

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

Try to avoid COUNT(*) statements on a table using InnoDB engine.

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

Add index only when needed. Do not add index unnecessarily and hope it will increase performance

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

Always use EXPLAIN to analyse the performance of a query

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.