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

Performance degredation when IN elements are string instead of int

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.5.12, 10.6.8
    • N/A
    • Optimizer
    • None
    • Debian 11

    Description

      I upgraded from Debian 10 to Debian 11. Debian 10 had mariadb 10.3.31 and Debian 11 has mariadb 10.5.12.
      Since the upgrade, queries with IN statement that include single quoted numbers (making them text instead of int) have very lower performance.

      I attach two example queries, one with quotes, one without. I will write them here cut down, with the explain output for both mariadb 10.3.31 and 10.5.12

      10.3.31 with quotes
      explain SELECT * FROM video
      WHERE id in ('1283','1284','1298', [ about 14000 ids deleted] , '105149','105152','105153')
      LIMIT 0, 14;
      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE video range PRIMARY PRIMARY 4 NULL 14488 Using where
      takes 0.05 sec

      10.3.31 without quotes
      explain SELECT * FROM video
      WHERE id in (1283,1284,1298, [ about 14000 ids deleted] , 105149,105152,105153)
      LIMIT 0, 14;
      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY video ALL PRIMARY NULL NULL NULL 14898
      1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
      2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 14488
      3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
      takes about 0.01 sec

      10.5.12 with quotes
      explain SELECT * FROM video
      WHERE id in ('1283','1284','1298', [ about 14000 ids deleted] , '105149','105152','105153')
      LIMIT 0, 14;
      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE video ALL PRIMARY NULL NULL NULL 14889 Using where
      Takes about 2,3 seconds

      10.5.12 without quotes
      explain SELECT * FROM video
      WHERE id in (1283,1284,1298, [ about 14000 ids deleted] , 105149,105152,105153)
      LIMIT 0, 14;
      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY video ALL PRIMARY NULL NULL NULL 14889
      1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
      2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 14488
      3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
      takes 0.01 sec

      So the first problem is the different optimization for the query with the quotes, resulting in 2,3 seconds execution instead of 0,05 seconds in mariadb 10.3.31. 10.3.31 uses range while 10.5.12 does not. Second problem is that mariadb is not doing IN predicate for single quoted integers, although I thought that MDEV-21265 solved this (apparently it didn't).

      Attachments

        1. test.sql
          204 kB
          Panagiotis Malakoudis

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              malakudi Panagiotis Malakoudis
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.