[MDEV-9133] range chosen for update while ref used for select on multicolumn index with length Created: 2015-11-16  Updated: 2016-01-22  Resolved: 2016-01-22

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9132 document "Using buffer" in extras of ... Closed

 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?

 Comments   
Comment by Elena Stepanova [ 2015-11-18 ]

Output from 10.0 commit 4008a3e55bda76fab622de163b174008d2d58abb

 
MariaDB [test]> 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 DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.60 sec)
 

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

MariaDB [test]> explain UPDATE radacct SET AcctUniqueId =1 WHERE AcctSessionId = '80504297' AND UserName = 'tZZZZZZ'\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: radacct
 
         type: range
 
possible_keys: UserName_AcctSessionId
 
          key: UserName_AcctSessionId
 
      key_len: 52
 
          ref: NULL
 
         rows: 1
 
        Extra: Using where
 
1 row in set (0.00 sec)

Comment by Sergei Petrunia [ 2016-01-22 ]

Note that both plans use key_len: 52. The scan the same range on the same key.

Short answer: The difference between range and ref is due to MySQL/MariaDB code internals and is not really meaningful.

Long version:
The primary purpose of ref access is to compute equi-join operations in JOINs. The SELECT in this example has only one table, so it is not really a join. Still, SELECTs are processed by JOIN runtime code, so the query plan uses ref access. The ref column has ref: const,const, so it's a degenerate kind of ref access.

Code that handles single-table UPDATE doesn't ever need to compute equijoins. Because of that, it never constructs ref access. It may find it useful to scan key range(s) to find rows, so it can (and does) construct range access.

If you change UPDATE to be a multi-table UPDATE, like:

 explain UPDATE radacct,one_row_table SET AcctUniqueId =1 WHERE AcctSessionId = '80504297' AND UserName = 'tZZZZZZ'\G

then ref access method will be picked/used, like in SELECT.

Comment by Sergei Petrunia [ 2016-01-22 ]

Closing as Not a Bug. Feel free to re-open if there is anything unresolved.

Generated at Thu Feb 08 07:32:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.