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

Query with very large IN clause lists runs slowly.

    XMLWordPrintable

Details

    Description

      Customer filed a support issue concerned that migrating from MySQL 5.6 to MariaDB 10.2.15 was causing a query that ran in 7 seconds in the old environment to run in ~300s in MariaDB. Testing side-by-side confirmed the difference in performance.

      The specific query and data-set are included in the referenced support case, and will be linked in an internal comment, and an abbreviated query is also included in comments. The full query is a union that contains two IN clauses with over 1000 items and two others with over 68,000 items in each, totaling 138,180 items listed in IN clauses.

      Testing revealed that MariaDB 10.2.15 runs approximately 40x longer than MySQL 5.6 to return the results, and 10.3.8 runs 20x longer than MySQL 5.6 in the same operation.

      The following information is tested on MariaDB 10.2.15

      Increasing max_heap_table_size, tmp_table_size, and key_buffer_size substantially (to 128M each) had no noticeable effect on performance, and toggling every available optimization switch resulted in variances of no more than 12% in execution speed.

      Changing default_tmp_storage_engine to MEMORY and ISAM had no significant effect on performance either.

      The following optimization switches were toggled and resulted in no meaningful performance change (all in MariaDb 10.2.15)

      condition_pushdown_for_derived=off  717 sec
      derived_merge=off  719 sec
      derived_with_keys=off  713 sec
      engine_condition_pushdown=on  738 sec
      exists_to_in=off  735 sec
      extended_keys=off  711 sec
      firstmatch=off  712 sec
      index_condition_pushdown=off  728 sec
      index_merge=off  745 sec
      index_merge_intersection=off  721 sec
      index_merge_sort_intersection=on  731 sec
      index_merge_sort_union=off  737 sec
      index_merge_union=off  755 sec
      in_to_exists=off  734 sec
      join_cache_bka=off  749 sec
      join_cache_hashed=off  740 sec
      join_cache_incremental=off  713 sec
      loosescan=off  745 sec
      materialization=off  737 sec
      mrr=on  718 sec
      mrr_cost_based=on  812 sec
      mrr_sort_keys=on  777 sec
      optimize_join_buffer_size=on  749 sec
      orderby_uses_equalities=off  809 sec
      outer_join_with_cache=off  728 sec
      partial_match_rowid_merge=off  736 sec
      partial_match_table_scan=off  725 sec
      semijoin=off  753 sec
      semijoin_with_cache=off  809 sec
      subquery_cache=off  795 sec
      table_elimination=off  733 sec
      

      Here is the MySQL 5.6 plan:

      [root@MySQL-5-6-34-216-202-163-RD ~]# echo "explain $(cat 20927_00_myQ.sql)" | mysql d20927
      *************************** 1. row ***************************
                 id: 1
        select_type: PRIMARY
              table: <derived2>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 15232
              Extra: NULL
      *************************** 2. row ***************************
                 id: 2
        select_type: DERIVED
              table: MVWorkLogEntry
               type: range
      possible_keys: StoreID,mPlanID,ClassName,mPlanStoreClassStatus
                key: mPlanID
            key_len: 4
                ref: NULL
               rows: 8704
              Extra: Using index condition; Using where
      *************************** 3. row ***************************
                 id: 3
        select_type: UNION
              table: MVWorkLogEntry
               type: range
      possible_keys: PRIMARY,StoreID,mPlanID,mPlanStoreClassStatus
                key: mPlanID
            key_len: 4
                ref: NULL
               rows: 8704
              Extra: Using index condition; Using where; Using temporary
      *************************** 4. row ***************************
                 id: 3
        select_type: UNION
              table: MVPendingWorkLogEntry
               type: eq_ref
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 4
                ref: d20927.MVWorkLogEntry.ID
               rows: 1
              Extra: Using where
      *************************** 5. row ***************************
                 id: NULL
        select_type: UNION RESULT
              table: <union2,3>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
              Extra: Using temporary
      

      the MariaDb 10.2.15 plan:

      [root@Maria-54-203-18-168-RD ~]# echo "explain $(cat 20927_10_myQ2.sql)" | mysql d20927
      *************************** 1. row ***************************
                 id: 1
        select_type: PRIMARY
              table: <derived2>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 3968
              Extra:
      *************************** 2. row ***************************
                 id: 2
        select_type: DERIVED
              table: MVWorkLogEntry
               type: range
      possible_keys: StoreID,mPlanID,ClassName,mPlanStoreClassStatus
                key: mPlanID
            key_len: 4
                ref: NULL
               rows: 2267
              Extra: Using index condition; Using where
      *************************** 3. row ***************************
                 id: 3
        select_type: UNION
              table: MVWorkLogEntry
               type: range
      possible_keys: PRIMARY,StoreID,mPlanID,mPlanStoreClassStatus
                key: mPlanID
            key_len: 4
                ref: NULL
               rows: 2267
              Extra: Using index condition; Using where; Using temporary
      *************************** 4. row ***************************
                 id: 3
        select_type: UNION
              table: MVPendingWorkLogEntry
               type: eq_ref
      possible_keys: PRIMARY,Closed
                key: PRIMARY
            key_len: 4
                ref: d20927.MVWorkLogEntry.ID
               rows: 1
              Extra: Using where; Distinct
      *************************** 5. row ***************************
                 id: NULL
        select_type: UNION RESULT
              table: <union2,3>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
              Extra:
      

      ...and the extended MariaDB 10.3.8 plan with warnings:

      [root@Maria-34-217-194-169-RD ~]# echo "explain extended $(cat 20927_10_myQ2.sql); show warnings;" | mysql d20927
      *************************** 1. row ***************************
                 id: 1
        select_type: PRIMARY
              table: <derived2>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 8
           filtered: 100.00
              Extra:
      *************************** 2. row ***************************
                 id: 2
        select_type: DERIVED
              table: <derived7>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 2
           filtered: 100.00
              Extra: Start temporary
      *************************** 3. row ***************************
                 id: 2
        select_type: DERIVED
              table: <derived5>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 2
           filtered: 100.00
              Extra: Using join buffer (flat, BNL join)
      *************************** 4. row ***************************
                 id: 2
        select_type: DERIVED
              table: MVWorkLogEntry
               type: ref
      possible_keys: StoreID,mPlanID,ClassName,mPlanStoreClassStatus
                key: mPlanStoreClassStatus
            key_len: 10
                ref: tvc_0.75333,tvc_1.198963,const
               rows: 1
           filtered: 100.00
              Extra: Using index condition; End temporary
      *************************** 5. row ***************************
                 id: 7
        select_type: DERIVED
              table: NULL
               type: NULL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
           filtered: NULL
              Extra: No tables used
      *************************** 6. row ***************************
                 id: 5
        select_type: DERIVED
              table: NULL
               type: NULL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
           filtered: NULL
              Extra: No tables used
      *************************** 7. row ***************************
                 id: 3
        select_type: UNION
              table: <derived11>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 2
           filtered: 100.00
              Extra: Start temporary; Using temporary
      *************************** 8. row ***************************
                 id: 3
        select_type: UNION
              table: <derived9>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 2
           filtered: 100.00
              Extra: Using join buffer (flat, BNL join)
      *************************** 9. row ***************************
                 id: 3
        select_type: UNION
              table: MVWorkLogEntry
               type: ref
      possible_keys: PRIMARY,StoreID,mPlanID,mPlanStoreClassStatus
                key: mPlanStoreClassStatus
            key_len: 8
                ref: tvc_0.75333,tvc_1.198963
               rows: 1
           filtered: 100.00
              Extra: Using index condition; End temporary
      *************************** 10. row ***************************
                 id: 3
        select_type: UNION
              table: MVPendingWorkLogEntry
               type: eq_ref
      possible_keys: PRIMARY,Closed
                key: PRIMARY
            key_len: 4
                ref: d20927.MVWorkLogEntry.ID
               rows: 1
           filtered: 100.00
              Extra: Using where; Distinct
      *************************** 11. row ***************************
                 id: 11
        select_type: DERIVED
              table: NULL
               type: NULL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
           filtered: NULL
              Extra: No tables used
      *************************** 12. row ***************************
                 id: 9
        select_type: DERIVED
              table: NULL
               type: NULL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
           filtered: NULL
              Extra: No tables used
      *************************** 13. row ***************************
                 id: NULL
        select_type: UNION RESULT
              table: <union2,3>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: NULL
           filtered: NULL
              Extra:
      Level	Code	Message
      Note	1003	/* select#1 */ select `don`.`LogEntryID` AS `LogEntryID`,`don`.`ClassName` AS `ClassName`,`don`.`LastEdited` AS `LastEdited`,`don`.`RepID` AS `RepID`,`don`.`StoreID` AS `StoreID`,`don`.`mPlanID` AS `mPlanID`,`don`.`Status` AS `Status` from (/* select#2 */ select `d20927`.`MVWorkLogEntry`.`ID` AS `LogEntryID`,`d20927`.`MVWorkLogEntry`.`ClassName` AS `ClassName`,`d20927`.`MVWorkLogEntry`.`LastEdited` AS `LastEdited`,`d20927`.`MVWorkLogEntry`.`RepID` AS `RepID`,`d20927`.`MVWorkLogEntry`.`StoreID` AS `StoreID`,`d20927`.`MVWorkLogEntry`.`mPlanID` AS `mPlanID`,`d20927`.`MVWorkLogEntry`.`Status` AS `Status` from `d20927`.`MVWorkLogEntry` semi join ((values (75333),(75334),(75954),(76257),(76411),(76564),(76565),(76721),(76722),(76724),(76725),(76726),(76727),(76728),(76729),(76730),(76731),(76732),(76733
       
      00),(77801),(77802),(77803),(77804),(77805),(77806),(77807),(77808),(77809),(77811),(77937),(77968),(77969)) `tvc_0`) semi join ((values (198963),(208459),(213146),(217375),(160344),(165363),(169957),(175520),(221764),(226415),(231647),(235837),(239769),(180975),(186246),(191059),(198979),(208477),(213162),(
       
      8),(213145),(217374),(160343),(165362),(169956),(175519),(221763),(226414),(231646),(235836),(239768),(180974),(186245),(191058)) `tvc_1`) where `d20927`.`MVWorkLogEntry`.`ClassName` = 'MVWorkLogEntry' and `d20927`.`MVWorkLogEntry`.`mPlanID` = `tvc_0`.`75333` and `d20927`.`MVWorkLogEntry`.`StoreID` = `tvc_1`.`198963` union (/* select#3 */ select distinct `d20927`.`MVWorkLogEntry`.`ID` AS `LogEntryID`,`d20927`.`MVWorkLogEntry`.`ClassName` AS `ClassName`,`d20927`.`MVWorkLogEntry`.`LastEdited` AS `LastEdited`,`d20927`.`MVWorkLogEntry`.`RepID` AS `RepID`,`d20927`.`MVWorkLogEntry`.`StoreID` AS `StoreID`,`d20927`.`MVWorkLogEntry`.`mPlanID` AS `mPlanID`,`d20927`.`MVWorkLogEntry`.`Status` AS `Status` from `d20927`.`MVWorkLogEntry` semi join ((values (75333),(75334),(75954),(76257),(76411),(76564),(76565),(76721),(76722),
       
      3),(77804),(77805),(77806),(77807),(77808),(77809),(77811),(77937),(77968),(77969)) `tvc_0`) semi join ((values (198963),(208459),(213146),(217375),(160344),(165363),(169957),(175520),(221764),(226415),(231
       
      (226398),(231630),(235820),(239752),(180956),(186225),(191040),(198961),(208458),(213145),(217374),(160343),(165362),(169956),(175519),(221763),(226414),(231646),(235836),(239768),(180974),(186245),(191058)) `tvc_1`) straight_join `d20927`.`MVPendingWorkLogEntry` where `d20927`.`MVPendingWorkLogEntry`.`Closed` = 0 and `d20927`.`MVWorkLogEntry`.`mPlanID` = `tvc_0`.`75333` and `d20927`.`MVWorkLogEntry`.`StoreID` = `tvc_1`.`198963` and `d20927`.`MVPendingWorkLogEntry`.`ID` = `d20927`.`MVWorkLogEntry`.`ID` and `d20927`.`MVWorkLogEntry`.`Status` <> 'paused')) `don`
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.