mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-02-07 11:19:06 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/partition_index_scan,desc.rdiff
Yuchen Pei 17629b4b56
 MDEV-37330 [to-squash] Address github review input 
- basic implementation of bitmap_get_prev_set
- a testcase to improve coverage
- handle multiple PARTITION BY RANGE COLUMNS in Case 1, with the
  limitation that all keys should have the same direction
2025-12-01 19:32:29 +11:00

479 lines
10 KiB
Text
Raw Permalink Blame History

@@ -1,7 +1,7 @@
# Index scans with PARTITION BY RANGE
CREATE TABLE t1 (
c int,
-INDEX idx1(c )
+INDEX idx1(c desc)
) PARTITION BY RANGE COLUMNS(c) (
PARTITION `p1` VALUES LESS THAN (10),
PARTITION `p2` VALUES LESS THAN (20),
@@ -17,9 +17,9 @@
1
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 1
+Handler_read_first 0
Handler_read_key 0
-Handler_read_last 0
+Handler_read_last 1
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
@@ -33,11 +33,11 @@
2
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 1
+Handler_read_first 0
Handler_read_key 0
-Handler_read_last 0
-Handler_read_next 1
-Handler_read_prev 0
+Handler_read_last 1
+Handler_read_next 0
+Handler_read_prev 1
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -63,11 +63,11 @@
16
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 2
+Handler_read_first 0
Handler_read_key 0
-Handler_read_last 0
-Handler_read_next 15
-Handler_read_prev 0
+Handler_read_last 2
+Handler_read_next 0
+Handler_read_prev 15
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -83,9 +83,9 @@
50
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 0
+Handler_read_first 1
Handler_read_key 0
-Handler_read_last 1
+Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
@@ -99,11 +99,11 @@
49
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 0
+Handler_read_first 1
Handler_read_key 0
-Handler_read_last 1
-Handler_read_next 0
-Handler_read_prev 1
+Handler_read_last 0
+Handler_read_next 1
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -129,11 +129,11 @@
35
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 0
+Handler_read_first 2
Handler_read_key 0
-Handler_read_last 2
-Handler_read_next 0
-Handler_read_prev 15
+Handler_read_last 0
+Handler_read_next 15
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -159,8 +159,8 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 6
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 6
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -185,8 +185,8 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 0
-Handler_read_prev 6
+Handler_read_next 6
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -201,7 +201,7 @@
CREATE TABLE t1 (
c int,
d INT,
-INDEX idx1(c , d )
+INDEX idx1(c desc, d desc)
) PARTITION BY RANGE COLUMNS(c, d) (
PARTITION `p1` VALUES LESS THAN (1, 1),
PARTITION `p2` VALUES LESS THAN (2, 2),
@@ -231,11 +231,11 @@
4 1
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 4
+Handler_read_first 0
Handler_read_key 0
-Handler_read_last 0
-Handler_read_next 15
-Handler_read_prev 0
+Handler_read_last 4
+Handler_read_next 0
+Handler_read_prev 15
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -265,11 +265,11 @@
2 5
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 0
+Handler_read_first 3
Handler_read_key 0
-Handler_read_last 3
-Handler_read_next 0
-Handler_read_prev 15
+Handler_read_last 0
+Handler_read_next 15
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -351,7 +351,7 @@
CREATE TABLE t1 (
c int,
d INT,
-INDEX idx1(c , d desc)
+INDEX idx1(c desc, d )
) PARTITION BY RANGE COLUMNS(c, d) (
PARTITION `p1` VALUES LESS THAN (1, 1),
PARTITION `p2` VALUES LESS THAN (2, 2),
@@ -449,11 +449,11 @@
2 1
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 0
+Handler_read_first 5
Handler_read_key 0
-Handler_read_last 5
-Handler_read_next 0
-Handler_read_prev 15
+Handler_read_last 0
+Handler_read_next 15
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -483,11 +483,11 @@
4 5
show status like 'handler_read_%';
Variable_name Value
-Handler_read_first 5
+Handler_read_first 0
Handler_read_key 0
-Handler_read_last 0
-Handler_read_next 15
-Handler_read_prev 0
+Handler_read_last 5
+Handler_read_next 0
+Handler_read_prev 15
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -500,7 +500,7 @@
CREATE TABLE t1 (
c int,
d INT,
-INDEX idx1(c , d)
+INDEX idx1(c desc, d)
) PARTITION BY RANGE COLUMNS(c) (
PARTITION `p1` VALUES LESS THAN (10),
PARTITION `p2` VALUES LESS THAN (20),
@@ -513,7 +513,7 @@
flush status;
select * from t1 where d = 113 ORDER BY d LIMIT 1;
c d
-1 113
+50 113
show status like 'handler_read_%';
Variable_name Value
Handler_read_first 1
@@ -536,7 +536,7 @@
CREATE TABLE t1 (
a INT,
b INT,
-KEY ( a , b )
+KEY ( a desc, b )
) PARTITION BY RANGE COLUMNS(a) (
PARTITION `p1` VALUES LESS THAN (10),
PARTITION `p2` VALUES LESS THAN (20),
@@ -558,9 +558,9 @@
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
-Handler_read_rnd 0
+Handler_read_rnd 2
Handler_read_rnd_deleted 0
-Handler_read_rnd_next 0
+Handler_read_rnd_next 3
FLUSH status;
SELECT a, MIN(b) FROM t1 WHERE a > 16 and a < 25 GROUP BY a;
a MIN(b)
@@ -577,8 +577,8 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 8
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 8
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -600,9 +600,9 @@
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
-Handler_read_rnd 0
+Handler_read_rnd 2
Handler_read_rnd_deleted 0
-Handler_read_rnd_next 0
+Handler_read_rnd_next 3
FLUSH status;
SELECT a, MAX(b) FROM t1 WHERE a > 16 and a < 25 GROUP BY a;
a MAX(b)
@@ -619,8 +619,8 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 8
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 8
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -635,7 +635,7 @@
CREATE TABLE t1 (
a INT,
b INT,
-KEY ( a, b )
+KEY ( a, b desc)
) PARTITION BY RANGE COLUMNS(b) (
PARTITION `p1` VALUES LESS THAN (10),
PARTITION `p2` VALUES LESS THAN (20),
@@ -651,7 +651,7 @@
show status like 'handler_read_%';
Variable_name Value
Handler_read_first 0
-Handler_read_key 6
+Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
@@ -667,7 +667,7 @@
CREATE TABLE t1 (
c int,
d INT,
-INDEX idx1(c, d )
+INDEX idx1(c, d desc)
) PARTITION BY RANGE COLUMNS(d) (
PARTITION `p1` VALUES LESS THAN (10),
PARTITION `p2` VALUES LESS THAN (20),
@@ -688,8 +688,8 @@
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
-Handler_read_next 2
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 2
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -725,8 +725,8 @@
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
-Handler_read_next 0
-Handler_read_prev 1
+Handler_read_next 1
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -755,8 +755,8 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 0
-Handler_read_prev 15
+Handler_read_next 15
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -791,8 +791,8 @@
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
-Handler_read_next 1
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 1
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -821,16 +821,16 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 15
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 15
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 0
select * from t1 where c = 120 LIMIT 2;
c d
-120 1
-120 2
+120 50
+120 49
### select * from t1 where c = 120 ORDER BY d LIMIT 2
set @js='$out';
select json_extract(@js,'$**.partitions_index_scan_method');
@@ -862,8 +862,8 @@
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
-Handler_read_next 0
-Handler_read_prev 15
+Handler_read_next 15
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -892,8 +892,8 @@
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
-Handler_read_next 15
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 15
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -902,7 +902,7 @@
CREATE TABLE t1 (
c1 int, c2 int,
d INT,
-INDEX idx1(c1, c2, d )
+INDEX idx1(c1, c2, d desc)
) PARTITION BY RANGE COLUMNS(d) (
PARTITION `p1` VALUES LESS THAN (10),
PARTITION `p2` VALUES LESS THAN (20),
@@ -937,8 +937,8 @@
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
-Handler_read_next 0
-Handler_read_prev 15
+Handler_read_next 15
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -953,7 +953,7 @@
CREATE TABLE t1 (
c int,
d INT,
-INDEX idx1(c, d )
+INDEX idx1(c, d desc)
) PARTITION BY RANGE (d % 5) (
PARTITION `p1` VALUES LESS THAN (1),
PARTITION `p2` VALUES LESS THAN (2),
@@ -987,8 +987,8 @@
Handler_read_first 0
Handler_read_key 5
Handler_read_last 0
-Handler_read_next 0
-Handler_read_prev 1
+Handler_read_next 1
+Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -1003,8 +1003,8 @@
Handler_read_first 0
Handler_read_key 5
Handler_read_last 0
-Handler_read_next 1
-Handler_read_prev 0
+Handler_read_next 0
+Handler_read_prev 1
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
@@ -1013,8 +1013,8 @@
### Unordered scan (filesort + mrr)
select * from t1 where c = 120 ORDER BY d % 5 LIMIT 2;
c d
-120 10
-120 15
+120 40
+120 45
show status like 'handler_read_%';
Variable_name Value
Handler_read_first 0
@@ -1029,8 +1029,8 @@
flush status;
select * from t1 where c = 120 LIMIT 2;
c d
-120 1
-120 2
+120 50
+120 49
show status like 'handler_read_%';
Variable_name Value
Handler_read_first 0
@@ -1064,7 +1064,7 @@
CREATE TABLE t2 (
taken int,
id int,
-PRIMARY KEY (id,taken ),
+PRIMARY KEY (id,taken desc),
KEY taken (taken)
)
PARTITION BY RANGE (taken)
@@ -1104,7 +1104,7 @@
Handler_read_first 1
Handler_read_key 4
Handler_read_last 0
-Handler_read_next 6
+Handler_read_next 3
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Reference in a new issue View git blame Copy permalink