6

We had a small malfunction which generated unexpected amount of data for a device. Our code ends up creating a select query along the lines of SELEXT * from XXX WHERE yyy IN (xxx,xxx,xxx....,xxxx) That should fetch data of last 5 days which normally is not a problem at all as the WHERE field is indexed.

We suddenly started having large amount of slow queries and bogging the server down. The queries were running at around 5000 seconds before we aborted them. On figuring out what was happening it turns out that if we have a query with maximum of 33594 parameters within the IN () clause, everything works fine and query finishes in 200ms. If we add one parameter the select seems to be running forever. We haven't let one to run indefinetly until it completes so I'm not sure if it will complete or not but they seem to last at least hours...

The data within the select is about 361.04KB. We're running on Google cloud SQL with MYSQL MYSQL_8_0_31

Any ideas on what might be causing this behaviour?

6
  • 2
    Save your values list to indexed temptable then join it...
    – Akina
    Apr 25, 2023 at 9:45
  • 1
    Thanks. Yep that could be one option. I'm more curious about why this happens to understand what's the limit to better understand the system and thus better be able to understand what workaround/fix is the best.
    – Tuxie
    Apr 25, 2023 at 11:50
  • Is that a Google database engine? Or is it MySQL?
    – Rick James
    Apr 25, 2023 at 15:14
  • Did you try EXPLAIN?
    – Alexander
    Apr 25, 2023 at 17:53
  • 1
    Read: dev.mysql.com/doc/refman/8.0/en/… Apr 25, 2023 at 18:46

2 Answers 2

1

At my company we have had the problem since MySQL 5.7 that there's a cap on the memory the optimizer can use, according to the option range_optimizer_max_mem_size. If you have too many items in the list, the optimizer gives up, and decides to do a table-scan instead of use the index.

See https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#range-optimization-memory-use

Our solution was to set this in the my.cnf:

range_optimizer_max_mem_size=0

It means there is no memory limit for the optimizer, so it's up to the developer to avoid a list so long that it causes memory faults. But it's more likely to use an index.

This has fixed the issue in every case where a long list was causing a table-scan.

5

I'm more curious about why this happens to understand what's the limit...

The why is because it's an overly complicated predicate. The IN clause is syntactical sugar for a bunch of OR clauses. Too many OR clauses or too many values in an IN clause results in an overly complicated predicate that the database engine is unable to parse and generate an efficient query plan for. Instead, the query plan being generated is now likely scanning the entire table in an inefficient manner.

There is no fixed hard-coded limit when this happens, and it will vary from query to query.

...to better understand the system and thus better be able to understand what workaround/fix is the best.

~33,000 values in an IN clause is an unreasonable amount. Heck, even 100 values in my mind is unreasonable, and in my opinion such a query design is an anti-pattern.

There's a few main ways to re-write the query that can fix the issue:

  1. Break the query up into multiple copies of the query with much smaller IN clauses and UNION them back together. This is probably also unrealistic to do with so many values in your IN clause though.

  2. Put the values in some sort of table, such as a temporary table, and use a JOIN to it from the table you're trying to filter down. This is typically more relationally efficient.

3
  • Thanks for the suggestions. There actually seems to be a limit for when the db engine starts doing full table scans. This one was posted by Bill in the comments of the question: dev.mysql.com/doc/refman/8.0/en/… And yep I agree with you on the coding improvements. In this case Laravel was doing stupid things and it wasn't visible until this issue.
    – Tuxie
    Apr 26, 2023 at 15:11
  • @Tuxie Good to know about that setting as well but please keep in mind Bill's comment on "so it's up to the developer to avoid a list so long that it causes memory faults" which goes back to the point of that setting existing by design to keep things sane. Even with that change, you're still at risk of regressing again due to what I mentioned, which is also just a universal reasonability limitation of pretty much all relational database system engines. (Also, you may find the query runs even faster by substituting the IN clause with a temporary table, possibly.)
    – J.D.
    Apr 26, 2023 at 15:56
  • 1
    Yep fully agree. In our case this was pure edge case with about 1000x the normal data amount and even then the memory size was just ~10% too small. Anyways now we also know what to watch for and spot incorrect code and can optimize.
    – Tuxie
    Apr 27, 2023 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.