[MDEV-6235] EXPLAIN UPDATE shows range when it should be eq_ref Created: 2014-05-13  Updated: 2014-05-16  Resolved: 2014-05-15

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.10
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Arjen Lentz Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: upstream
Environment:

All,Linux


 Description   

EXPLAIN UPDATE shows access method 'range' for a scenario where both parts of the primary key are referenced, with single constants.
For SELECT that would result in access method 'const' (as shown below also). For UPDATE I'd expect it to do eq_ref.

In fact, the query execution uses Handler_read_key and thus accesses only a single row. My conclusion: EXPLAIN UPDATE shows the wrong access method, while the actual query execution does the right thing.

Schema below (from Drupal)

explain UPDATE xmlsitemap SET subtype='navigation', loc='node/%/log', language='und', access='0', status='0', status_override='0', lastmod='0', priority='0.5', priority_override='0', changefreq='0', changecount='0'\nWHERE (type = 'menu_link') AND (id = '636');
 
+------+-------------+------------+-------+----------------------+---------+---------+------+------+-------------+
 
| id   | select_type | table      | type  | possible_keys        | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+-------+----------------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | xmlsitemap | range | PRIMARY,type_subtype | PRIMARY | 102     | NULL |    1 | Using where |
 
+------+-------------+------------+-------+----------------------+---------+---------+------+------+-------------+
 

explain select * from xmlsitemap where  (type = 'menu_link') AND (id = '636');
 
+------+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------+
 
| id   | select_type | table      | type  | possible_keys        | key     | key_len | ref         | rows | Extra |
 
+------+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------+
 
|    1 | SIMPLE      | xmlsitemap | const | PRIMARY,type_subtype | PRIMARY | 102     | const,const |    1 |       |
 
+------+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------+
 

CREATE TABLE `xmlsitemap` (
 
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Primary key with type; a unique id for the item.',
 
  `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'Primary key with id; the type of item (e.g. node, user, etc.).',
 
  `subtype` varchar(128) NOT NULL DEFAULT '' COMMENT 'A sub-type identifier for the link (node type, menu name, term VID, etc.).',
 
  `loc` varchar(255) NOT NULL DEFAULT '' COMMENT 'The URL to the item relative to the Drupal path.',
 
  `language` varchar(12) NOT NULL DEFAULT '' COMMENT 'The languages.language of this link or an empty string if it is language-neutral.',
 
  `access` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'A boolean that represents if the item is viewable by the anonymous user. This field is useful to store the result of node_access() so we can retain changefreq and priority_override information.',
 
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'An integer that represents if the item is included in the sitemap.',
 
  `status_override` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean that if TRUE means that the status field has been overridden from its default value.',
 
  `lastmod` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp of last modification of the item.',
 
  `priority` float DEFAULT NULL COMMENT 'The priority of this URL relative to other URLs on your site. Valid values range from 0.0 to 1.0.',
 
  `priority_override` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean that if TRUE means that the priority field has been overridden from its default value.',
 
  `changefreq` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The average time in seconds between changes of this item.',
 
  `changecount` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The number of times this item has been changed. Used to help calculate the next changefreq value.',
 
  PRIMARY KEY (`id`,`type`),
 
  KEY `loc` (`loc`),
 
  KEY `access_status_loc` (`access`,`status`,`loc`),
 
  KEY `type_subtype` (`type`,`subtype`),
 
  KEY `language` (`language`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The base table for xmlsitemap links.'



 Comments   
Comment by Elena Stepanova [ 2014-05-13 ]

Same is true for MySQL 5.6:

MySQL [test]> explain UPDATE xmlsitemap SET subtype='navigation', loc='node/%/log', language='und', access='0', status='0', status_override='0', lastmod='0', priority='0.5', priority_override='0', changefreq='0', changecount='0'\nWHERE (type = 'menu_link') AND (id = '636');
 
PAGER set to stdout
 
+----+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------------+
 
| id | select_type | table      | type  | possible_keys        | key     | key_len | ref         | rows | Extra       |
 
+----+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------------+
 
|  1 | SIMPLE      | xmlsitemap | range | PRIMARY,type_subtype | PRIMARY | 102     | const,const |    1 | Using where |
 
+----+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------------+
 
1 row in set (0.10 sec)
 
 
 
MySQL [test]> 
MySQL [test]> explain select * from xmlsitemap where  (type = 'menu_link') AND (id = '636');
 
+----+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------+
 
| id | select_type | table      | type  | possible_keys        | key     | key_len | ref         | rows | Extra |
 
+----+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------+
 
|  1 | SIMPLE      | xmlsitemap | const | PRIMARY,type_subtype | PRIMARY | 102     | const,const |    1 | NULL  |
 
+----+-------------+------------+-------+----------------------+---------+---------+-------------+------+-------+
 
1 row in set (0.05 sec)
 
 
 
MySQL [test]> select @@version;
 
+--------------+
 
| @@version    |
 
+--------------+
 
| 5.6.17-debug |
 
+--------------+
 
1 row in set (0.00 sec)

Comment by Sergei Petrunia [ 2014-05-15 ]

The difference between "range" and "const" is relevant when optimizing joins. "const" tables are read before the join optimization is done, and then the optimizer takes advantage of knowing the values of const_table.column.

Comment by Sergei Petrunia [ 2014-05-15 ]

"range" access can also increment Handler_read_key counter. try

SELECT * FROM tbl WHERE pk IN (const1, const2, const3)

Comment by Sergei Petrunia [ 2014-05-15 ]

Execution of

SELECT FROM tbl WHERE pk=const

will use join_read_const_table(). EXPLAIN correctly reflects that.
Execution of

UPDATE tbl SET ... WHERE pk=const

will use QUICK_RANGE_SELECT. EXPLAIN correctly reflects that.

So, this is not an EXPLAIN bug.

Comment by Sergei Petrunia [ 2014-05-15 ]

One could argue that single-table SELECT and UPDATE should use the same execution paths. There are some differences between UPDATE and SELECT execution, but there are way more common things than different things.

I was re-writing MariaDB from scratch today, I would have made UPDATE and SELECT use the same execution path. But in the current state, I don't think it is worth to rewrite UPDATE/DELETE to use SELECT's execution path.

Comment by Sergei Petrunia [ 2014-05-15 ]

Closing. Feel free to reopen if the points in the comments do not address the issue.

Comment by Arjen Lentz [ 2014-05-16 ]

No that's fine Sergei - of course I'm aware of how const works.
It just seemed that eq_ref better covered the situation than range, but you've done a fine explanation of what the optimiser does - you know your stuff there and obviously EXPLAIN shows what the optimiser does and so I agree that it's not a bug.
thanks

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