How to Optimize MySQL Queries for Speed and Performance on Alibaba Cloud ECS

Prerequisites

  1. A valid Alibaba cloud account. If you don’t have one already, you can sign up for an Alibaba Cloud and enjoy $300 worth in Free Trial.
  2. A server running your favourite operating system that can support MySQL (e.g. Ubuntu, Centos, Debian).
  3. MySQL database server.
  4. A MySQL user capable of running root commands.

Tip #1: Index All Columns Used in ‘where’, ‘order by’ and ‘group by’ Clauses

Apart from guaranteeing uniquely identifiable records, an index allows MySQL server to fetch results faster from a database. An index is also very useful when it comes to sorting records.

mysql> select customer_id, customer_name from customers where customer_id='140385';
mysql> explain select customer_id, customer_name from customers where customer_id='140385';+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 500 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Explain select customer_id, customer_name from customers where customer_id='140385';+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customers | NULL | ref | customer_id | customer_id | 13 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

Tip 2: Optimize Like Statements with Union Clause

Sometimes, you may want to run queries using the comparison operator ‘or’ on different fields or columns in a particular table. When the ‘or’ keyword is used too much in where clause, it might make the MySQL optimizer to incorrectly choose a full table scan to retrieve a record.

mysql> select * from students where first_name like  'Ade%'  or last_name like 'Ade%' ;
mysql> select  from students where first_name like  'Ade%'  union all select  from students where last_name like  'Ade%' ;

Tip 3: Avoid Like Expressions with Leading Wildcards

MySQL is not able to utilize indexes when there is a leading wildcard in a query. If we take our example above on the students table, a search like this will cause MySQL to perform full table scan even if you have indexed the ‘first_name’ field on the students table.

mysql> select * from students where first_name like  '%Ade'  ;
mysql> explain select * from students where first_name like  '%Ade'  ;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | students | NULL | ALL | NULL | NULL | NULL | NULL | 500 | 11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Tip 4: 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 underperform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.

mysql>Alter table students ADD FULLTEXT (first_name, last_name);
mysql>Select * from students where match(first_name, last_name) AGAINST ('Ade');
mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | students | NULL | fulltext | first_name | first_name | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

Tip 6: Optimize Your Database Schema

Even if you optimize your MySQL queries and fail to come up with a good database structure, your database performance can still halt when your data increases.

Normalize Tables

First, normalize all database tables even if it will involve some trade-offs. For instance if you are creating two tables to hold customers data and orders, you should reference the customer on the orders table using the customer id as opposed to repeating the customer’s name on the orders table. The latter will cause your database to bloat.

Use Optimal Data Types

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

Avoid Null Values

Null is the absence of any value in a column. You should avoid this kind of values whenever 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 ‘ifnull’ statement to return alternative value if a record is null.

Avoid Too Many Columns

Wide tables can be extremely expensive and require more CPU time to process. If possible, don’t go above a hundred unless your business logic specifically calls for this.

Optimize Joins

Always include fewer tables in your join statements. An SQL statement with poorly designed pattern that involves a lot of joins may not work well. A rule of thumb is to have utmost a dozen joins for each query.

Tip 7: MySQL Query Caching

If your website or application performs a lot of select queries (e.g. WordPress), you should take advantage of MySQL query caching feature. This will speed up performance when read operations are conducted.

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in <b>set</b> (0.00 sec)

Setting the MySQL Server Query Cache

You can set the MySQL query cache values by editing the configuration file (‘/etc/mysql/my.cnf’ or ‘/etc/mysql/mysql.conf.d/mysqld.cnf’). This will depend on your MySQL installation. Don’t set a very large query cache size value because this will degrade the MySQL server due to cached overhead and locking. Values in the range of tens of megabytes are recommended.

mysql> show variables like 'query_cache_%' ;
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in <b>set</b> (0.00 sec)
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k

Conclusion

In this guide, we have showed you how to optimize your MySQL server hosted on Alibaba cloud for speed and performance. We believe that the guide will allow you to craft better queries and have a well-structured database structure that will not only be simple to maintain but also offer more stability to your software applications or website. Remember, if you haven’t tried Alibaba Cloud, you can create an account and enjoy $300 worth in Free Trial.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com