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

LP:637962 - Considerable performance regression on certain queries in maria-5.1-wl24

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 5.3.10
    • None

    Description

      The following query:

      SELECT `col_varchar_64_key` FROM `table100000_myisam_int_autoinc`
      WHERE ( `col_varchar_64_key` NOT IN ( 'now' , 'rsgxnnowvz' ) OR `col_varchar_64_key` LIKE CONCAT ('Utah' , '%' ) )
      AND ( `col_varchar_10_key` BETWEEN 'cr' AND 'really' OR `col_varchar_64_key` IN ( 'j' , 'rcrsgxnn' ) )
      AND ( ( `col_varchar_10_key` != 'it' ) OR `col_varchar_10_key` IS NULL )
      ORDER BY `col_varchar_64_key` LIMIT 7;

      Takes no time on maria-5.1 but takes over 1 second on maria-5.1-wl24 . Even if the filesort is avoided by removing the ORDER BY, considerable performance difference remains.

      EXPLAIN under maria-5.1:

      id: 1
      select_type: SIMPLE
      table: table100000_myisam_int_autoinc
      type: range
      possible_keys: col_varchar_10_key,col_varchar_64_key
      key: col_varchar_64_key
      key_len: 67
      ref: NULL
      rows: 99905
      Extra: Using where

      EXPLAIN under maria-5.1-wl24

      id: 1
      select_type: SIMPLE
      table: table100000_myisam_int_autoinc
      type: index_merge
      possible_keys: col_varchar_10_key,col_varchar_64_key
      key: col_varchar_10_key,col_varchar_64_key
      key_len: 13,67
      ref: NULL
      rows: 54298
      Extra: Using sort_union(col_varchar_10_key,col_varchar_64_key); Using where; Using filesort

      The table contains 100K rows and will be uploaded shortly.

      Attachments

        1. LPexportBug637962_bug637962.diff
          6 kB
          Rasmus Johansson
        2. LPexportBug637962_bug637962-2.test
          171 kB
          Rasmus Johansson
        3. LPexportBug637962.xml
          12 kB
          Rasmus Johansson

        Activity

          Launchpad bug id: 637962

          ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 637962

          This is a serious performance degradation that can be observed for queries with
          ORDER BY a LIMIT n
          when there is an index on the field a and a range condition over field a,
          but at the same time optimizer chooses an index merge scan.

          The problem is that when making a cost-based choice between an index scan and an index-merge scan the optimizer does not take into account ORDER BY ...LIMIT n.

          igor Igor Babaev (Inactive) added a comment - This is a serious performance degradation that can be observed for queries with ORDER BY a LIMIT n when there is an index on the field a and a range condition over field a, but at the same time optimizer chooses an index merge scan. The problem is that when making a cost-based choice between an index scan and an index-merge scan the optimizer does not take into account ORDER BY ...LIMIT n.

          I'm afraid we don't have enough time to fix the problem for 5.3.10. The fixing patch will require a good testing.
          I

          igor Igor Babaev (Inactive) added a comment - I'm afraid we don't have enough time to fix the problem for 5.3.10. The fixing patch will require a good testing. I

          The problem is observed in mysql-5.6.7 (but not in prior releases).
          I've reported the bug http://bugs.mysql.com/67432.

          igor Igor Babaev (Inactive) added a comment - The problem is observed in mysql-5.6.7 (but not in prior releases). I've reported the bug http://bugs.mysql.com/67432 .

          The bug was fixed and the fix was pushed into 5.3.

          igor Igor Babaev (Inactive) added a comment - The bug was fixed and the fix was pushed into 5.3.

          People

            igor Igor Babaev (Inactive)
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.