How TOXIC can SQL toxic queries be?

Most of us don’t really care how efficient the SQL we wrote, as long as it work, why bother? But we didn’t aware of one single toxic query statement can make our SQL database go beyond 2 to 3 times slower, or even more.

So, what actually toxic query do I mean? Toxic Query is a

SQL statement that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined

which also known as Slow Query. In a nutshell, Toxic Query is SQL query that took a long time to perform.

You may wonder how serious can SQL statements slowing down your database? Let’s figure it out together with an experiment.

In this experiment, we have :

• MacBook Pro with M1 Max CPU (32GB Ram)
• Go (Fairly fast programming language)
• MySQL 8.0.28
• Storage Engine (InnoDB)
• 500000 record set
• sqlike (Go SQL ORM)

INSERT with Stored Procedure

1.37 x slower than the normal query

Stored Procedure

LIKE with Leading Wildcard

1.38 x slower than the optimised query

LIKE statement

Offset-based Pagination

152.57 x slower than the optimised query

Pagination

For full result, you may go to the link below.

GitHub - si3nloong/sql-toxic-queries: Benchmark on toxic queries

If you want to know more about the performance tuning on your SQL database, you may refer to my previous article.

In conclusion, some toxic queries can be extremely slow, try to follow the best practices before you implement it.