Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.21
-
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
- relates to
-
MDEV-9132 document "Using buffer" in extras of explain output - https://mariadb.com/kb/en/mariadb/explain/
- Closed