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

XML

Word

Printable

Details

Type: Technical task
Status: Closed (View Workflow)
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 |

# +----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Attachments

Activity

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

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.