Learn ways to optimize SQL queries

Tram Ho

We programmers must work a lot with SQL queries every day, so it is important to understand how to optimize SQL statements because it will help our system performance increase significantly, reducing waiting time from users. The article will show you ways to optimize these statements.

1. Type index in columns using where, order by, group by

  • Besides ensuring uniqueness of records, indexing allows the MySQL server to get results faster. Indexes can take up memory resources and reduce performance when inserting, deleting, and updating. However, in cases where the table has many records (> 10), they can help reduce the time for select statements. Let’s look at the example below when running an SQL statement with 500 rows without using an index

When using EXPLAIN to see the details of the query, we see some important information:

  • MySQL searched the entire table to get the record whose id was originally passed based on column type: ALL, rows: 500
    To correct the above query, just type index in customer_id column

Rerun the above query will produce results

We can see that the query has been optimized because we only need to browse on 1 rows without having to browse the whole table as above. The selection and indexing also need to be carefully selected to avoid wasting resources and not bring performance to the system. See more here

2. Optimize the statement with Union

Sometimes we also need to run comparative queries with ‘like’, ‘or’. When using ‘or’ too much, mysql might have to search the entire table to search for the record. Union can make queries faster, especially in the case of logical indexing. Example in the case below

The above query has been further optimized by using union to take advantage of the typed index

3. Avoid using queries with the same likes as ‘%’ in front

Consider the query below

Use EXPLAIN to see more details

As you can see that the index doesn’t work in this case, the query still needs to browse the entire table to look for records that satisfy the request. In this case, there are 2 options to consider if you really need to use% in front, otherwise remove it or consider using a full-text index.

4. Use the query of MySQL Full-text search

In the case of querying with the wildcards operator as above, we can consider using MySQL full-text search (FTS) for better performance. To add a full-text search index, use the command

In the example above, we have specified 2 columns that we want to match (first_name, last_name) to search with (‘A’). Running EXPLAIN has the following result

It can be seen that the query was optimized when only having to browse 1 rows, not the entire table as before.

5. Avoid using indexed columns with functions

Consider the following query

We use the YEAR function together with the finished_at column, which will not allow the database to use the index in the finished_at column because it indexes the value of finished_at and not YEAR (finished_at).
To avoid this, we can use the index for the function using generated columns
Or alternatively find a way to rewrite the equivalent query without resorting to a function

6. Optimize database schema

Optimize data types

MySQL supports many different data types: integer, float, double, date, date_time, Varchar, text … When designing tables we should follow the principle of ” shorter is always better
For example, when designing a user table that contains less than 100 records, we should use ‘TINYINT’ for the user_id column. Or in the case of columns related to dates (order_date), using the type is date_time will be most reasonable because we will not write anything more to convert. If any column needs a number, it should be a numeric type like integer instead of char, varchar because MySQL will calculate better in case of numeric type when compared with varchar.

Avoid null values

We should avoid storing null values ​​in the data table as this may produce unexpected results. For example, in the case of calculating the amount of an order that a column contains null, it may lead to unexpected results that we must use the condition “if not null”. In this case consider using default values ​​for value.

7. Use MySQL query caching

If the system uses multiple select statements, we can consider using mysql query caching which will increase the read speed of the system. However, in cases where a lot of updates are needed, it may reduce performance. It is possible to check if the Mysql server is using the query cache or not by running the command

Seting mysql query cache

  • We can set mysql query cache by editing file ‘/etc/mysql/my.cnf’ or ‘/etc/mysql/mysql.conf.d/mysqld.cnf’ . To check the value of the query cache we use the command

It is possible to change the above value by changing in the config file

8. Other notes

Only select the columns that are absolutely necessary

Instead of using SELECT *, we should select only the columns that need to be used, this will help increase query performance during work.

Use an inner join instead of an outer join when possible

Using outer joins too much will reduce the query performance significantly instead of using inner joins, in similar cases use inner joins instead of outer joins.

Use DISTINCT and UNION only when needed

Using union and distinct in case of unnecessary might lead to a decrease in query performance. Instead of using UNION, you can use UNION ALL for better results.

Avoid using conditions with different types of styles

For example, when comparing with where one side is a varchar and a numeric type, the index will not work because it will have to be implicitly cast to identify the data. In this case, trying to make the same comparison in the first place will yield better results.

Reference

https://www.eversql.com/sql-performance-tuning-tips-for-mysql-query-optimization/
https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html
https://www.alibabacloud.com/blog/how-to-optimize-mysql-queries-for-speed-and-performance-on-alibaba-cloud-ecs_593872
https://www.cloudways.com/blog/mysql-performance-tuning/#wild

Share the news now

Source : Viblo