LevelDB storage engine (MDEV-3841)

[MDEV-3969] Queries where execution plans on LevelDB don't use PK while on MyISAM they do Created: 2012-12-23  Updated: 2012-12-24  Resolved: 2012-12-24

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

Type: Technical task Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: leveldb

Issue Links:
Relates

 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 |
 
# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+



 Comments   
Comment by Sergei Petrunia [ 2012-12-24 ]

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).

Generated at Thu Feb 08 06:52:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.