mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
7e4233746e
Allows index condition pushdown for reverse ordered scans, a previously disabled feature due to poor performance. This patch adds a new API to the handler class called set_end_range which allows callers to tell the handler what the end of the index range will be when scanning. Combined with a pushed index condition, the handler can scan the index efficiently and not read beyond the end of the given range. When checking if the pushed index condition matches, the handler will also check if scanning has reached the end of the provided range and stop if so. If we instead only enabled ICP for reverse ordered scans without also calling this new API, then the handler would perform unnecessary index condition checks. In fact this would continue until the end of the index is reached. These changes are agnostic of storage engine. That is, any storage engine that supports index condition pushdown will inhereit this new behavior as it is implemented in the SQL and storage engine API layers. The partitioned tables storage meta-engine (ha_partition) adds an override of set_end_range which recursively calls set_end_range on its child storage engine (handler) implementations. This commit updates the test made in an earlier commit to show that ICP matches happen for the reverse ordered case. This patch is based on changes written by Olav Sandstaa in MySQL commit da1d92fd46071cd86de61058b6ea39fd9affcd87
101 lines
3.2 KiB
Text
101 lines
3.2 KiB
Text
SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
|
|
SET GLOBAL innodb_stats_persistent=OFF;
|
|
#
|
|
# MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used
|
|
#
|
|
CREATE TABLE t2 (
|
|
pk1 int(11) NOT NULL,
|
|
pk2 int(11) NOT NULL,
|
|
fd5 bigint(20) DEFAULT NULL,
|
|
filler1 char(200),
|
|
filler2 char(200),
|
|
PRIMARY KEY (pk1,pk2),
|
|
UNIQUE KEY ux_pk1_fd5 (pk1,fd5)
|
|
) ENGINE=InnoDB;
|
|
insert into t2
|
|
select
|
|
round(log(2,seq+1)),
|
|
seq,
|
|
seq,
|
|
REPEAT('filler-data-', 10),
|
|
REPEAT('filler-data-', 10)
|
|
from
|
|
seq_0_to_1999;
|
|
analyze table t2 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
select pk1, count(*) from t2 group by pk1;
|
|
pk1 count(*)
|
|
0 1
|
|
1 1
|
|
2 3
|
|
3 6
|
|
4 11
|
|
5 23
|
|
6 45
|
|
7 91
|
|
8 181
|
|
9 362
|
|
10 724
|
|
11 552
|
|
# The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13)
|
|
EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 range ux_pk1_fd5 ux_pk1_fd5 13 NULL 138 Using index condition
|
|
# This also must use range, not ref. key_len must be 13
|
|
EXPLAIN SELECT * FROM t2 WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 range PRIMARY,ux_pk1_fd5 ux_pk1_fd5 13 NULL 138 Using index condition
|
|
drop table t2;
|
|
#
|
|
# MDEV-6814: Server crashes in calculate_key_len on query with ORDER BY
|
|
#
|
|
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f2),KEY(f2,f1)) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1,5,0),(2,6,0);
|
|
SELECT * FROM t1 WHERE f1 < 3 AND f2 IS NULL ORDER BY f1;
|
|
f1 f2 f3
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-6796: Unable to skip filesort when using implicit extended key
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk1 int(11) NOT NULL,
|
|
pk2 varchar(64) NOT NULL,
|
|
col1 varchar(16) DEFAULT NULL,
|
|
PRIMARY KEY (pk1,pk2),
|
|
KEY key1 (pk1,col1)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE t2 (
|
|
pk1 int(11) NOT NULL,
|
|
pk2 varchar(64) NOT NULL,
|
|
col1 varchar(16) DEFAULT NULL,
|
|
PRIMARY KEY (pk1,pk2),
|
|
KEY key1 (pk1,col1,pk2)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
INSERT INTO `t1` VALUES
|
|
(12321321,'a8f5f167f44f4964e6c998dee827110c','video'),
|
|
(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'),
|
|
(12321321,'wwafdsafdsafads','video'),
|
|
(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'),
|
|
(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'),
|
|
(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'),
|
|
(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd');
|
|
insert into t2 select * from t1;
|
|
# this must not use filesort:
|
|
explain SELECT pk2
|
|
FROM t1 USE INDEX(key1)
|
|
WHERE pk1 = 123
|
|
AND col1 = 'video'
|
|
ORDER BY pk2 DESC LIMIT 21;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1 key1 55 const,const 1 Using where; Using index
|
|
# this must not use filesort, either:
|
|
explain SELECT pk2
|
|
FROM t2 USE INDEX(key1)
|
|
WHERE pk1 = 123 AND col1 = 'video'
|
|
ORDER BY pk2 DESC LIMIT 21;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ref key1 key1 55 const,const 1 Using where; Using index
|
|
drop table t1, t2;
|
|
SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
|