Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24173

After minor upgrade to 10.2.35 queries with large IN clauses have performance issues

    XMLWordPrintable

    Details

      Description

      Since upgrading from 10.2.34 to 10.2.35 queries using IN clauses with many values have performance issues.

      For example, a query like this one but with many different values on the last IN clause:

      select distinct record from redcap_record_list where project_id = 128 and dag_id in ('2080') and record in ('1', '7') order by sort;

      Takes 0.2 seconds on 10.2.34 and more than 100 seconds on 10.2.35 . It also allocates a lot of RAM on 10.2.35 ( more than 3 GB ) while on 10.2.34 does not seem to.

      The table engine is InnoDB. Same happens with query cache enabled or disabled. Configuration is the same on both versions.

      Same issue happens on all upgraded servers, on different applications, with different queries, ...

      It's not PHP version dependant, same issue happens when running queries from "mysql command" on CLI.

      Issue is resolved if downgrading to 10.2.34

      I am attaching the complete query extracted from slow query log.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              guebs Aitor
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration