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

    XMLWordPrintable

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

          People

            igor Igor Babaev
            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.