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

Optimizer prefers rowid filter instead of primary key

    XMLWordPrintable

Details

    Description

      The file test.sql is mysqldump of tables subscription,label,track,license_track

      Load in the tables query from the mysql command line

      The problem query:
      MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
      -------------------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -------------------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE subscription ref account_id,license_id,account_id_2 account_id 4 const 1 Using index
      1 SIMPLE track ref PRIMARY,label_id,enabled_2,enabled enabled_2 1 const 16754  
      1 SIMPLE label eq_ref PRIMARY,enabled PRIMARY 4 test.track.label_id 1 Using where
      1 SIMPLE license_track eq_ref license_id,track_id,license_id_2 license_id 8 test.subscription.license_id,test.track.id 1 Using index

      -------------------------------------------------------------------------------------------------------------------------------------------------+

      Execute:
      optimize table track;
      optimize table license_track;

      After this the explain shows:
      MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE subscription ref account_id,license_id,account_id_2 account_id 4 const 1 Using index
      1 SIMPLE license_track ref license_id,track_id,license_id_2 license_id 4 test.subscription.license_id 20055 Using index
      1 SIMPLE track ALL filter PRIMARY,label_id,enabled_2,enabled   enabled_2   1 NULL 39744 (50%) Using where; Using join buffer (flat, BNL join); Using rowid filter
      1 SIMPLE label eq_ref PRIMARY,enabled PRIMARY 4 test.track.label_id 1 Using where

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      The bug is that track is using rowid filter instead of lookup of primary key, like in the first explain

      In 10.5 we get the following "correct" explain for the exact same 10.4 tables:
      -----------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -----------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE subscription ref account_id,license_id,account_id_2 account_id 4 const 1 Using index
      1 SIMPLE license_track ref license_id,track_id,license_id_2 license_id 4 test.subscription.license_id 20055 Using index
      1 SIMPLE track eq_ref PRIMARY,label_id,enabled_2,enabled PRIMARY 4 test.license_track.track_id 1 Using where
      1 SIMPLE label eq_ref PRIMARY,enabled PRIMARY 4 test.track.label_id 1 Using where

      -----------------------------------------------------------------------------------------------------------------------------------+

      I noticed that when one gets the 'wrong' explain in 10.4, one will get a crash when executing the following query:

      SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription LEFT JOIN license_track ON license_track.license_id=subscription.license_id LEFT JOIN track ON track.id=license_track.track_id LEFT JOIN label ON label.id=track.label_id WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7;

      #4 0x00000000010ed6d5 in row_search_idx_cond_check (mysql_rec=0x7fff840bcd50 "ÿ\001", prebuilt=0x7fff840c0820, rec=0x7fffe13a007d "\200", offsets=0x7ffff4253e60) at /my/maria-10.4/storage/innobase/row/row0sel.cc:3942
      #5 0x00000000010f13da in row_search_mvcc (buf=0x7fff840bcd50 "ÿ\001", mode=PAGE_CUR_G, prebuilt=0x7fff840c0820, match_mode=0, direction=0) at /my/maria-10.4/storage/innobase/row/row0sel.cc:5257
      #6 0x0000000000f05342 in ha_innobase::index_read (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /my/maria-10.4/storage/innobase/handler/ha_innodb.cc:9305
      #7 0x0000000000f062f4 in ha_innobase::index_first (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001") at /my/maria-10.4/storage/innobase/handler/ha_innodb.cc:9679
      #8 0x0000000000f064cc in ha_innobase::rnd_next (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001") at /my/maria-10.4/storage/innobase/handler/ha_innodb.cc:9772
      #9 0x0000000000bd5256 in handler::ha_rnd_next (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001") at /my/maria-10.4/sql/handler.cc:2826
      #10 0x0000000000d76cb1 in rr_sequential (info=0x7fff84147f98) at /my/maria-10.4/sql/records.cc:477
      #11 0x00000000007cc56d in READ_RECORD::read_record (this=0x7fff84147f98) at /my/maria-10.4/sql/records.h:69
      #12 0x00000000008fa3ed in join_init_read_record (tab=0x7fff84147ed0) at /my/maria-10.4/sql/sql_select.cc:21129
      #13 0x0000000000a4701e in JOIN_TAB_SCAN::open (this=0x7fff84022e60) at /my/maria-10.4/sql/sql_join_cache.cc:3349
      #14 0x0000000000a4543e in JOIN_CACHE::join_matching_records (this=0x7fff84167fd0, skip_last=false) at /my/maria-10.4/sql/sql_join_cache.cc:2252
      #15 0x0000000000a44f38 in JOIN_CACHE::join_records (this=0x7fff84167fd0, skip_last=false) at /my/maria-10.4/sql/sql_join_cache.cc:2088
      #16 0x00000000008f7b53 in sub_select_cache (join=0x7fff840226a0, join_tab=0x7fff84147ed0, end_of_records=false) at /my/maria-10.4/sql/sql_select.cc:19986
      #17 0x00000000008f870c in evaluate_join_record (join=0x7fff840226a0, join_tab=0x7fff84147b28, error=0) at /my/maria-10.4/sql/sql_select.cc:20408
      #18 0x00000000008f8134 in sub_select (join=0x7fff840226a0, join_tab=0x7fff84147b28, end_of_records=false) at /my/maria-10.4/sql/sql_select.cc:20227
      #19 0x00000000008f870c in evaluate_join_record (join=0x7fff840226a0, join_tab=0x7fff84147780, error=0) at /my/maria-10.4/sql/sql_select.cc:20408
      #20 0x00000000008f7f3f in sub_select (join=0x7fff840226a0, join_tab=0x7fff84147780, end_of_records=false) at /my/maria-10.4/sql/sql_select.cc:20188

      This happens for table 'track'.

      Attachments

        Activity

          People

            igor Igor Babaev
            monty Michael Widenius
            Votes:
            1 Vote for this issue
            Watchers:
            4 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.