[MDEV-3969] Queries where execution plans on LevelDB don't use PK while on MyISAM they do - JIRA

Details

Type: Technical task
Status: Closed
Priority: Minor
Resolution: Not a Bug
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- leveldb

Description

As briefly mentioned on IRC before, I've collected some examples of queries where a PK is not used (according to EXPLAIN), while for an identical MyISAM table it is. I'm not sure if any of these signify bugs, either at this point or in general – maybe it's all by design, – please take a look to decide on that.

Example 1


CREATE TABLE `t1_myisam` (
  `b` binary(1) DEFAULT NULL,
  `b20` binary(20) NOT NULL,
  `v16` varbinary(16) DEFAULT NULL,
  `v128` varbinary(128) DEFAULT NULL,
  PRIMARY KEY (`b20`)
) ENGINE=MyISAM;

INSERT INTO t1_myisam (b,b20,v16,v128) VALUES 
('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),
('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');

CREATE TABLE `t1_leveldb` (
  `b` binary(1) DEFAULT NULL,
  `b20` binary(20) NOT NULL,
  `v16` varbinary(16) DEFAULT NULL,
  `v128` varbinary(128) DEFAULT NULL,
  PRIMARY KEY (`b20`)
) ENGINE=LevelDB;

INSERT INTO t1_leveldb (b,b20,v16,v128) VALUES 
('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),
('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');

EXPLAIN SELECT HEX(b20) FROM t1_myisam ORDER BY b20;

# +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
# | id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
# +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
# |  1 | SIMPLE      | t1_myisam | index | NULL          | PRIMARY | 20      | NULL |    4 | Using index |
# +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

EXPLAIN SELECT HEX(b20) FROM t1_leveldb ORDER BY b20;

# +----+-------------+------------+------+---------------+------+---------+------+------+----------------+
# | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
# +----+-------------+------------+------+---------------+------+---------+------+------+----------------+
# |  1 | SIMPLE      | t1_leveldb | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using filesort |
# +----+-------------+------------+------+---------------+------+---------+------+------+----------------+

Example 2


CREATE TABLE `t2_myisam` (
  `b` binary(1) NOT NULL,
  `b20` binary(20) DEFAULT NULL,
  `v16` varbinary(16) DEFAULT NULL,
  `v128` varbinary(128) NOT NULL,
  UNIQUE KEY `b_v` (`b`,`v128`)
) ENGINE=MyISAM;

INSERT INTO t2_myisam (b,b20,v16,v128) VALUES 
('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),
('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');

CREATE TABLE `t2_leveldb` (
  `b` binary(1) NOT NULL,
  `b20` binary(20) DEFAULT NULL,
  `v16` varbinary(16) DEFAULT NULL,
  `v128` varbinary(128) NOT NULL,
  UNIQUE KEY `b_v` (`b`,`v128`)
) ENGINE=LevelDB;

INSERT INTO t2_leveldb (b,b20,v16,v128) VALUES 
('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),
('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');

EXPLAIN SELECT HEX(b), HEX(v128) FROM t2_myisam WHERE b != 'a' AND v128 > 'varchar';

# +----+-------------+-----------+-------+---------------+------+---------+------+------+--------------------------+
# | id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
# +----+-------------+-----------+-------+---------------+------+---------+------+------+--------------------------+
# |  1 | SIMPLE      | t2_myisam | index | b_v           | b_v  | 131     | NULL |    4 | Using where; Using index |
# +----+-------------+-----------+-------+---------------+------+---------+------+------+--------------------------+

EXPLAIN SELECT HEX(b), HEX(v128) FROM t2_leveldb WHERE b != 'a' AND v128 > 'varchar';

# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# |  1 | SIMPLE      | t2_leveldb | ALL  | b_v           | NULL | NULL    | NULL | 1000 | Using where |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Same query but with USE INDEX:

EXPLAIN SELECT HEX(b), HEX(v128) FROM t2_leveldb USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar';

# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# |  1 | SIMPLE      | t2_leveldb | ALL  | b_v           | NULL | NULL    | NULL | 1000 | Using where |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Same tables, different query:

EXPLAIN SELECT HEX(v128), COUNT(*) FROM t2_myisam GROUP BY HEX(v128);

# +----+-------------+-----------+-------+---------------+------+---------+------+------+----------------------------------------------+
# | id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
# +----+-------------+-----------+-------+---------------+------+---------+------+------+----------------------------------------------+
# |  1 | SIMPLE      | t2_myisam | index | NULL          | b_v  | 131     | NULL |    4 | Using index; Using temporary; Using filesort |
# +----+-------------+-----------+-------+---------------+------+---------+------+------+----------------------------------------------+

EXPLAIN SELECT HEX(v128), COUNT(*) FROM t2_leveldb GROUP BY HEX(v128);

# +----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
# | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
# +----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
# |  1 | SIMPLE      | t2_leveldb | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using temporary; Using filesort |
# +----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

Example 3

CREATE TABLE `t3_myisam` (
  `c` char(1) DEFAULT NULL,
  `c20` char(20) DEFAULT NULL,
  `v16` varchar(16) NOT NULL,
  `v128` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`v16`)
) ENGINE=MyISAM;

INSERT INTO t3_myisam (c,c20,v16,v128) VALUES 
('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),
('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');

CREATE TABLE `t3_leveldb` (
  `c` char(1) DEFAULT NULL,
  `c20` char(20) DEFAULT NULL,
  `v16` varchar(16) NOT NULL,
  `v128` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`v16`)
) ENGINE=LevelDB;

INSERT INTO t3_leveldb (c,c20,v16,v128) VALUES 
('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),
('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');

EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t3_myisam WHERE v16 LIKE 'varchar%';

# +----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
# | id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
# +----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
# |  1 | SIMPLE      | t3_myisam | index | PRIMARY       | PRIMARY | 18      | NULL |    2 | Using where; Using index |
# +----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t3_leveldb WHERE v16 LIKE 'varchar%';

# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# |  1 | SIMPLE      | t3_leveldb | ALL  | PRIMARY       | NULL | NULL    | NULL | 1000 | Using where |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Example 4

CREATE TABLE `t4_myisam` (
  `d` date NOT NULL,
  `dt` datetime DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t` time NOT NULL,
  `y` year(4) DEFAULT NULL,
  PRIMARY KEY `d_t` (`d`,`t`)
) ENGINE=MyISAM;

INSERT INTO t4_myisam (d,dt,ts,t,y) VALUES
('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000'),
('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001'),
('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999'),
('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998'),
('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994');

CREATE TABLE `t4_leveldb` (
  `d` date NOT NULL,
  `dt` datetime DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t` time NOT NULL,
  `y` year(4) DEFAULT NULL,
  PRIMARY KEY `d_t` (`d`,`t`)
) ENGINE=LevelDB;

INSERT INTO t4_leveldb (d,dt,ts,t,y) VALUES
('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000'),
('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001'),
('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999'),
('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998'),
('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994');

EXPLAIN SELECT d, t FROM t4_myisam WHERE CONCAT(d,' ',t) != CURRENT_DATE();

# +----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
# | id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
# +----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
# |  1 | SIMPLE      | t4_myisam | index | NULL          | PRIMARY | 6       | NULL |    5 | Using where; Using index |
# +----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

EXPLAIN SELECT d, t FROM t4_leveldb WHERE CONCAT(d,' ',t) != CURRENT_DATE();

# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
# |  1 | SIMPLE      | t4_leveldb | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

Sergei Petrunia added a comment - 2012-12-24 12:15

So, the differences are between "ALL" and "index" plans. It is ok, because it is not possible to do "key-only" reads in LevelDB. We intend support index-only scans for secondary indexes (which are not yet implemented).

Show

Sergei Petrunia added a comment - 2012-12-24 12:15 So, the differences are between "ALL" and "index" plans. It is ok, because it is not possible to do "key-only" reads in LevelDB. We intend support index-only scans for secondary indexes (which are not yet implemented).

People

Assignee:

Sergei Petrunia

Reporter:

Elena Stepanova

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2012-12-23 04:21

Updated:

2012-12-24 12:18

Resolved:

2012-12-24 12:18

Agile

View on Board