66 lines
2.3 KiB
Plaintext
66 lines
2.3 KiB
Plaintext
CREATE TABLE t1
|
|
(
|
|
/* fields/keys for row retrieval tests */
|
|
key1 INT,
|
|
key2 INT,
|
|
key3 INT,
|
|
key4 INT,
|
|
/* make rows much bigger then keys */
|
|
filler1 CHAR(200),
|
|
KEY(key1),
|
|
KEY(key2)
|
|
) ENGINE=XENGINE;
|
|
CREATE TABLE t0 AS SELECT * FROM t1;
|
|
# Printing of many insert into t0 values (....) disabled.
|
|
# Printing of many insert into t1 select .... from t0 disabled.
|
|
# Printing of many insert into t1 (...) values (....) disabled.
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
7201
|
|
SET GLOBAL xengine_force_flush_memtable_now = 1;
|
|
EXPLAIN UPDATE t1 SET filler1='to be deleted' WHERE key1=100 AND key2=100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge key1,key2 key1,key2 5,5 # 1 100.00 Using intersect(key1,key2); Using where
|
|
UPDATE t1 SET filler1='to be deleted' WHERE key1=100 and key2=100;
|
|
DROP TABLE t0, t1;
|
|
DROP TABLE IF EXISTS test_index_merge;
|
|
CREATE TABLE test_index_merge (id INT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT, c3 INT, index idx_c1c2(c1,c2), index idx_c1c3(c1,c3)) ENGINE=xengine;
|
|
set session binlog_format=row;
|
|
show variables like '%2pc%';
|
|
Variable_name Value
|
|
xengine_enable_2pc ON
|
|
show variables like '%binlog_format%';
|
|
Variable_name Value
|
|
binlog_format ROW
|
|
CREATE PROCEDURE fill_table1(cnt int)
|
|
begin
|
|
declare i int default 0;
|
|
repeat insert into test_index_merge(c1,c2,c3) values(100, 50, 100);
|
|
set i=i+1;
|
|
until i > cnt
|
|
end repeat;
|
|
end $$
|
|
CREATE PROCEDURE fill_table2(cnt int)
|
|
begin
|
|
declare i int default 0;
|
|
repeat insert into test_index_merge(c1,c2,c3) values(100, 100, 50);
|
|
set i=i+1;
|
|
until i > cnt
|
|
end repeat;
|
|
end$$
|
|
call fill_table1(10000);
|
|
call fill_table2(10000);
|
|
insert into test_index_merge values(50002, 100, 50, 50);
|
|
set global xengine_force_flush_memtable_now=1;
|
|
analyze table test_index_merge;
|
|
Table Op Msg_type Msg_text
|
|
test.test_index_merge analyze status OK
|
|
explain update test_index_merge set id=59999 where c1=100 and c2 = 50 and c3 = 50;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE test_index_merge NULL index_merge idx_c1c2,idx_c1c3 idx_c1c3,idx_c1c2 10,10 NULL # 100.00 Using intersect(idx_c1c3,idx_c1c2); Using where; Using temporary
|
|
update test_index_merge set id=59999 where c1=100 and c2 = 50 and c3 = 50;
|
|
set sql_log_bin=0;
|
|
DROP TABLE test_index_merge;
|
|
DROP PROCEDURE fill_table1;
|
|
DROP PROCEDURE fill_table2;
|