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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.3.10
    • Component/s: None
    • Labels:

      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

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              philipstoev Philip Stoev (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: