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

range chosen for update while ref used for select on multicolumn index with length

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.0.21
    • N/A
    • Optimizer
    • None

    Description

      slow query log:
      # Thread_id: 161  Schema: hotspot  QC_hit: No
      # Query_time: 17.934323  Lock_time: 0.000119  Rows_sent: 0  Rows_examined: 4
      #
      # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      # explain: 1    SIMPLE  radacct range   AcctSessionId,UserName_AcctSessionId,UserStop   UserName_AcctSessionId  52      NULL    1       Using where; Using buffer
       
      MariaDB [hotspot]> explain UPDATE radacct           SET ..       WHERE AcctSessionId = '80504297'           AND UserName = 'tZZZZZZ'\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: radacct
               type: range
      possible_keys: AcctSessionId,UserName_AcctSessionId,UserStop
                key: UserName_AcctSessionId
            key_len: 52
                ref: NULL
               rows: 1
              Extra: Using where; Using buffer

      When a select form of the same query is used:

      MariaDB [hotspot]>explain select * from radacct     WHERE AcctSessionId = '80504297'           AND UserName = 'tZZZZZZ'\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: radacct
               type: ref
      possible_keys: AcctSessionId,UserName_AcctSessionId,UserStop
                key: UserName_AcctSessionId
            key_len: 52
                ref: const,const
               rows: 1
              Extra: Using where
      1 row in set (0.00 sec)

      CREATE TABLE `radacct` (
        `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,
        `AcctSessionId` varchar(32) NOT NULL DEFAULT '',
        `AcctUniqueId` varchar(32) NOT NULL DEFAULT '',
        `UserName` varchar(64) NOT NULL DEFAULT '',
      ....
        PRIMARY KEY (`RadAcctId`),
        KEY `UserName_AcctSessionId` (`UserName`(8),`AcctSessionId`(8)),
      ...
      ) ENGINE=InnoDB AUTO_INCREMENT=141873298 DEFAULT CHARSET=utf8

      (despite obscuring the username it was 7 characters)

      as both AcctSessionId and UserName are <= 8 characters could a ref lookup be used in the UPDATE statement exactly like the SELECT?

      related - MDEV-9132 - what does "Using buffer" mean?

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.