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

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

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> 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

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

Normalize Tables

Use Optimal Data Types

Avoid Null Values

Avoid Too Many Columns

Optimize Joins

Tip 7: MySQL Query Caching

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

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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Reflections on my first month at Makers

Making Sense of Unbounded Data

Final Project of Scrumming

Alibaba Cloud Named Leader in Gartner’s 2020 Magic Quadrant for Cloud Database Management Systems…

In Defense of Agile Methodologies

🎉ZOMBIE WORLD Z AMBASSADOR RESULT🎉

MongooseIM 3.3.0: Supporting happy relations

What is CI-CD Pipeline in an easy way?

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

More from Medium

Run a Spring boot docker image on windows with a MySQL local database connection

3D-Secure Application Renewal Process

The right way for Celery using mongo as backend

How to install Airflow locally using Docker

Airflow Dashboard