1# 2# test using Index Condition Pushdown for partitioned tables 3# 4# Test failure of ICP calls -> don't use ICP (MyISAM does not support 5# ICP on BLOB indexes) 6CREATE TABLE t1 (a int PRIMARY KEY, b BLOB, c varchar(16) DEFAULT 'Filler...', INDEX (b(4), a)) 7ENGINE = MyISAM 8PARTITION BY HASH (a) PARTITIONS 3; 9Warnings: 10Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 11SHOW CREATE TABLE t1; 12Table Create Table 13t1 CREATE TABLE `t1` ( 14 `a` int(11) NOT NULL, 15 `b` blob, 16 `c` varchar(16) DEFAULT 'Filler...', 17 PRIMARY KEY (`a`), 18 KEY `b` (`b`(4),`a`) 19) ENGINE=MyISAM DEFAULT CHARSET=latin1 20/*!50100 PARTITION BY HASH (a) 21PARTITIONS 3 */ 22Warnings: 23Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 24INSERT INTO t1 (a, b) VALUES (1, 0xdeadbeef), (2, "text filler"), 25(3, 'filler...'), (4, " more filler "), (5, "test text"), (6, "testing..."); 26ANALYZE TABLE t1; 27Table Op Msg_type Msg_text 28test.t1 analyze status OK 29test.t1 analyze warning The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 30EXPLAIN SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2); 31id select_type table partitions type possible_keys key key_len ref rows filtered Extra 321 SIMPLE t1 p0,p1,p2 range b b 7 NULL 6 100.00 Using where 33Warnings: 34Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 35Note 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 (`test`.`t1`.`a` % 2)) 36EXPLAIN FORMAT=JSON SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2); 37EXPLAIN 38{ 39 "query_block": { 40 "select_id": 1, 41 "cost_info": { 42 "query_cost": "9.41" 43 }, 44 "table": { 45 "table_name": "t1", 46 "partitions": [ 47 "p0", 48 "p1", 49 "p2" 50 ], 51 "access_type": "range", 52 "possible_keys": [ 53 "b" 54 ], 55 "key": "b", 56 "used_key_parts": [ 57 "b" 58 ], 59 "key_length": "7", 60 "rows_examined_per_scan": 6, 61 "rows_produced_per_join": 6, 62 "filtered": "100.00", 63 "cost_info": { 64 "read_cost": "8.21", 65 "eval_cost": "1.20", 66 "prefix_cost": "9.41", 67 "data_read_per_join": "240" 68 }, 69 "used_columns": [ 70 "a", 71 "b" 72 ], 73 "attached_condition": "((`test`.`t1`.`b` >= 'te') and (`test`.`t1`.`a` % 2))" 74 } 75 } 76} 77Warnings: 78Note 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 (`test`.`t1`.`a` % 2)) 79SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2); 80a HEX(b) 811 DEADBEEF 825 746573742074657874 83Only MyISAM and InnoDB supports both INDEX and BLOBS... 84ALTER TABLE t1 ENGINE = InnoDB; 85ANALYZE TABLE t1; 86Table Op Msg_type Msg_text 87test.t1 analyze status OK 88EXPLAIN SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2); 89id select_type table partitions type possible_keys key key_len ref rows filtered Extra 901 SIMPLE t1 p0,p1,p2 range b b 7 NULL 4 100.00 Using index condition; Using where 91Warnings: 92Note 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 (`test`.`t1`.`a` % 2)) 93EXPLAIN FORMAT=JSON SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2); 94EXPLAIN 95{ 96 "query_block": { 97 "select_id": 1, 98 "cost_info": { 99 "query_cost": "6.61" 100 }, 101 "table": { 102 "table_name": "t1", 103 "partitions": [ 104 "p0", 105 "p1", 106 "p2" 107 ], 108 "access_type": "range", 109 "possible_keys": [ 110 "b" 111 ], 112 "key": "b", 113 "used_key_parts": [ 114 "b" 115 ], 116 "key_length": "7", 117 "rows_examined_per_scan": 4, 118 "rows_produced_per_join": 4, 119 "filtered": "100.00", 120 "index_condition": "(`test`.`t1`.`a` % 2)", 121 "cost_info": { 122 "read_cost": "5.81", 123 "eval_cost": "0.80", 124 "prefix_cost": "6.61", 125 "data_read_per_join": "160" 126 }, 127 "used_columns": [ 128 "a", 129 "b" 130 ], 131 "attached_condition": "(`test`.`t1`.`b` >= 'te')" 132 } 133 } 134} 135Warnings: 136Note 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 (`test`.`t1`.`a` % 2)) 137SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2); 138a HEX(b) 1391 DEADBEEF 1405 746573742074657874 141DROP TABLE t1; 142