Debian Codegraph @

mysql-test/suite/parts/r/partition_icp.result

raw
1 #
2 # test using Index Condition Pushdown for partitioned tables
3 #
4 # Test behavior of ICP calls -> use ICP for InnoDB even for BLOB indexes.
5 CREATE TABLE t1 (a int PRIMARY KEY, b BLOB, c varchar(16) DEFAULT 'Filler...', INDEX (b(4), a))
6 PARTITION BY HASH (a) PARTITIONS 3;
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` int NOT NULL,
11 `b` blob,
12 `c` varchar(16) DEFAULT 'Filler...',
13 PRIMARY KEY (`a`),
14 KEY `b` (`b`(4),`a`)
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
16 /*!50100 PARTITION BY HASH (`a`)
17 PARTITIONS 3 */
18 INSERT INTO t1 (a, b) VALUES (1, 0xdeadbeef), (2, "text filler"),
19 (3, 'filler...'), (4, " more filler "), (5, "test text"), (6, "testing...");
20 ANALYZE TABLE t1;
21 Table Op Msg_type Msg_text
22 test.t1 analyze status OK
23 EXPLAIN SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
24 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
25 1 SIMPLE t1 p0,p1,p2 range b b 7 NULL 4 100.00 Using index condition; Using where
26 Warnings:
27 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`b`) AS `HEX(b)` from `test`.`t1` where ((`test`.`t1`.`b` >= 'te') and (0 <> (`test`.`t1`.`a` % 2)))
28 EXPLAIN FORMAT=JSON SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
29 EXPLAIN
30 {
31 "query_block": {
32 "select_id": 1,
33 "cost_info": {
34 "query_cost": "2.06"
35 },
36 "table": {
37 "table_name": "t1",
38 "partitions": [
39 "p0",
40 "p1",
41 "p2"
42 ],
43 "access_type": "range",
44 "possible_keys": [
45 "b"
46 ],
47 "key": "b",
48 "used_key_parts": [
49 "b"
50 ],
51 "key_length": "7",
52 "rows_examined_per_scan": 4,
53 "rows_produced_per_join": 4,
54 "filtered": "100.00",
55 "index_condition": "(0 <> (`test`.`t1`.`a` % 2))",
56 "cost_info": {
57 "read_cost": "1.66",
58 "eval_cost": "0.40",
59 "prefix_cost": "2.06",
60 "data_read_per_join": "352"
61 },
62 "used_columns": [
63 "a",
64 "b"
65 ],
66 "attached_condition": "(`test`.`t1`.`b` >= 'te')"
67 }
68 }
69 }
70 Warnings:
71 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`b`) AS `HEX(b)` from `test`.`t1` where ((`test`.`t1`.`b` >= 'te') and (0 <> (`test`.`t1`.`a` % 2)))
72 SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
73 a HEX(b)
74 1 DEADBEEF
75 5 746573742074657874
76 DROP TABLE t1;