Identify useless queries in MariaDB/MySQL

by | Sep 7, 2021 | MariaDB, MySQL

Do you want to improve your application’s and database server’s performance? Of course one of the most important things to do is to optimise the most impacting queries.

You have various ways to find them, depending on what exactly you mean by “most impacting”. You can have an approach oriented to your systems workload, or an approach oriented to your users experience. The former is what you typically do with tools like PMM, the latter is done with tools like New Relic. They are not mutually exclusive and they both are important, for different reasons.

But there is a third important approach: get rid of the queries that serve no purpose. Because they add work to the server, they probably make requests slower, they could lock rows, and they could consume enough CPU and memory to increase the cost of our servers.

MariaDB/MySQL performance_schema will help find those queries. It needs to be enabled.

Performance Schema and useless queries

I know, I know: you think you don’t have such queries. If query went to production, they must have a purpose. Well, chances are you’re wrong. In complex environments, normally in a production environment useless queries can be found. Maybe they were useful when they were written, but at some point they lost their purpose and no one removed them. Maybe they used to return data that don’t exist anymore, because a feature was removed or rows changed. Maybe they stopped working correctly after a table was modified. Maybe they never worked but no one ever noticed that because testing is insufficient.

Whatever the reason… let’s find them.

Queries that always return an error

Many queries return an error, but… only in certain situations. For example, some queries use the IN (1, 2, 3...) syntax, which is fine. But sometimes the list of values is empty, and the IN () syntax is not valid. Other examples include unquoted strings: if the string is a number, the query will work. But if it’s alphanumeric, it will fail. Of course those queries should be found and fixed.

But some queries always return an error. This happens if their syntax is not valid, and the syntax error does not depend on parameters. Or maybe parameters always cause errors (in the example above, maybe strings are always alphanumeric).

To find such queries, run:

    FROM performance_schema.events_statements_summary_by_digest
        AND COUNT_STAR > 20
    LIMIT 10

Some notes:

  • We are looking for completely useless queries. But after that, you may want to see queries whose ratio between COUNT_STAR and SUM_ERRORS is bad. Or even queries that returned at least one error.
  • We put a lower limit to COUNT_STAR to avoid false positives – queries that were executed by mistake, perhaps manually, and will never run again.
  • The limit on LAST_SEEN is also important. We want to exclude queries that don’t run anymore, or don’t fail anymore because the conditions that caused the error have changed.
  • Queries executed more times are obviously more important to erase.
Running queries that always fail with an error is not much different from Sisyphus eternal effort. This picture is from Highgate Cemetery, in London.

Queries that never return rows

Believe it or not, I often see queries that always return an empty resultset. If you find such queries, you should at least suspect that they are useless and can be removed.

All queries consume some resources. But queries that don’t return rows are much more important to remove than queries that fail. The reason is that these queries are executed. They return nothing, but they could examine a big amount of rows, materialise them in memory, or keep them locked.

Find them in this way:

    FROM performance_schema.events_statements_summary_by_digest
        AND SUM_ROWS_SENT = 0
        AND COUNT_STAR > 20
    LIMIT 10


  • See notes about the previous query.
  • We need to filter by DIGEST_TEXT because we are not interested in CREATEDROPSET, commands, and many others.
  • Writes (UPDATE, etc) do not return rows, but they do affect some rows (usually). We exclude them with SUM_ROWS_AFFECTED = 0.
  • Here I ordered the results by the number of rows examined because I consider it very significant about the amount of work cause by the query. Depending on which problems you have, you may want to filter by something else, for example SUM_CREATED_TMP_TABLES if you have memory consumption problems.

Empty queries

TL;DR: you hardly care about this.

A last type of useless queries is… empty queries, or queries with no text. A typical way to run them is to add a semicolon (;) at the end of a query. by mistake:

SELECT * FROM information_schema.TABLES;;

You may think it only happens when running queries manually, but occasionally I saw many empty queries in production. I suspect it’s caused by certain ORMs bugs.

However, finding empty queries could be time consuming for developers, and it’s hardly worth the effort. You may try to ask them to do so if the number of such queries is high (like, they happen multiple times a second), but I’m mentioning empty queries mainly for the sake of completeness.

We can spot empty queries more easily with the user_statistics plugin, by Percona – but it’s not necessary:

SHOW GLOBAL STATUS LIKE 'Com_empty_query';
SET GLOBAL userstat := 1;

What to do next

Talk to developers. There could be reasons why a query must not be eliminated. If the syntax is wrong, it can be fixed. If it returns no rows, that could be expected for some reason. If that’s the case, a comment could be added to indicate that the query is not useless, so the next time you check you won’t waste your time on it.

Many high level developers hate to optimise queries. But this particular type of problems has more chances to attract their attention. Their code does something potentially useless or wrong, and they usually will want to know why and fix the problem.

See also


Related articles:


When we put our hands on a new OLTP environment, we find these queries more often than not. Eliminating them helps reducing the resource usage and increase the speed of certain.

Finding queries that always fail or never return rows is part of our MariaDB Health Checks and MySQL Health Checks.

Federico Razzoli

All content in this blog is distributed under the Creative Commons Attribution 4.0 International license (CC BY 4.0). You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer.In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software.As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

MariaDB/MySQL: working with storage engines

MariaDB/MySQL: working with storage engines

MariaDB and MySQL support several storage engines. See MariaDB and MySQL storage engines: an overview for a discussion about existing MariaDB and MySQL storage engines. Here we'll see how to work with them. We'll see how to obtain information about storage engines,...

MariaDB and MySQL storage engines: an overview

MariaDB and MySQL storage engines: an overview

MySQL was the first DBMS to introduce the concept of storage engines in the early 2000s. This was one of its main features. Later, MariaDB extended the storage engine API and included some storage engine maintained by third parties as part of its official...

MySQL 8.0.33 : Quick Overview

MySQL 8.0.33 : Quick Overview

The MySQL Team has released MySQL 8.0.33 very recently on April 18, 2023. This release contains a number of bug fixes along with some interesting improvements. In this blog post, I'll cover the most important changes in MySQL 8.0.33. Variables can be set while...



Submit a Comment

Your email address will not be published. Required fields are marked *