How to improve a MySQL query performance

The most important thing about a good web application is its excellent access performance. Database MySQL is an integral part of web applications and an important part of determining their performance. So improving the performance of MySQL is crucial.

Here are some summaries of self-collecting about query optimization:

Reduce selected rows and columns

The most basic reason a query doesn’t perform well is because it’s working with too much data. So you need to find if your application is retrieving too many rows or columns more than you need and stop using SELECT *  or retrieve more rows than you need. 

Optimize your database schema

1-Normalize Tables

Normalization is the process of reducing data redundancy while maintain data integrity.

2-Avoid Too Many Columns

Instead of creating one wide table, consider splitting it apart in to logical structures. For instance, if you are creating a customer table but you realize a customer can have multiple addresses, it is better to create a separate table for holding customers addresses that refer back to the customers table using the 'customer_id' field.

3-Use Optimal Data Types

MySQL supports different data types including integer, float, double, date, date_time, Varchar, char, and text, among others. When designing your tables, you should know that "shorter is always better."

For instances, if a field used to store country code so you know the stored string length (2 chars) then you should use  CHAR type. If you have column will store small numbers like from 1 to 100 then you should use TINYINT because it will accommodate all your values from -128 to 128.

4-Avoid Null Values whenever possible

You should avoid null values as much as possible because they can harm your database results. For instance, if you want to get the sum of all orders in a database but a particular order record has a null amount, the expected result might misbehave unless you use MySQL 'Coalesce' statement to return alternative value if a record is null.

5-Optimize Joins

Try to use less joins in your SQL queries as much as you can. You can denormalize your DB schema by adding redundant data where you think it will help the most to avoid costly joins.

Create optimal indexes

Adding indexes to your database is in general speeding up your select queries. However, the index also has to be created and stored. So the update, delete and insert queries will be slower and it will cost you a bit more disk space. In general, you won’t notice the difference with deleting, updating and inserting if you have indexed your table correctly and therefore it’s advisable to add indexes at the right locations.

Take Advantage of MySQL Full-Text Searches

If you are faced with a situation where you need to search data using wildcards and you don't want your database to under perform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.

MySQL query caching

If your application is read intensive, you should take advantage of MySQL query caching feature. This will speed up performance when read operations are conducted. This cache the the select query and the result data set, then the result fetched from memory if they are executed more than once. However, if your application updates the table frequently, this will invalidate any cached query and result set.