202 lines
8.3 KiB
Plaintext
202 lines
8.3 KiB
Plaintext
DROP TABLE IF EXISTS t1,t2;
|
|
CREATE TABLE t1 (
|
|
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
|
|
a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
c INT NOT NULL,
|
|
d INT NOT NULL,
|
|
PRIMARY KEY pk (pk),
|
|
KEY idx_a (a),
|
|
KEY idx_b (b),
|
|
KEY idx_c (c),
|
|
KEY idx_d (d)
|
|
);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN UPDATE t1 SET a=2 WHERE b=6 AND c=6 ;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge idx_b,idx_c idx_b,idx_c 4,4 NULL # 100.00 Using intersect(idx_b,idx_c); Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`a` = 2 where ((`test`.`t1`.`c` = 6) and (`test`.`t1`.`b` = 6))
|
|
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 ;
|
|
COUNT(*) SUM(a)
|
|
15 90
|
|
UPDATE t1 SET a=2 WHERE b=6 AND c=6 ;
|
|
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 ;
|
|
COUNT(*) SUM(a)
|
|
15 30
|
|
EXPLAIN UPDATE t1 SET a=2 WHERE b=6 AND c=6 AND d=6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge idx_x,idx_x,idx_x idx_x,idx_x 4,4 NULL # 100.00 Using intersect(idx_x,idx_x); Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`a` = 2 where ((`test`.`t1`.`d` = 6) and (`test`.`t1`.`c` = 6) and (`test`.`t1`.`b` = 6))
|
|
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 AND d=6;
|
|
COUNT(*) SUM(a)
|
|
15 30
|
|
UPDATE t1 SET a=2 WHERE b=6 AND c=6 AND d=6;
|
|
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 AND d=6;
|
|
COUNT(*) SUM(a)
|
|
15 30
|
|
EXPLAIN UPDATE t1 SET c=6 WHERE d=6 AND a IS NOT NULL AND b=6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge idx_b,idx_d idx_b,idx_d 4,4 NULL # 100.00 Using intersect(idx_b,idx_d); Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`c` = 6 where ((`test`.`t1`.`b` = 6) and (`test`.`t1`.`d` = 6))
|
|
SELECT COUNT(*), SUM(c) FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
|
|
COUNT(*) SUM(c)
|
|
15 90
|
|
UPDATE t1 SET c=6 WHERE d=6 AND a IS NOT NULL AND b=6;
|
|
SELECT COUNT(*), SUM(c) FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
|
|
COUNT(*) SUM(c)
|
|
15 90
|
|
EXPLAIN UPDATE t1 SET b=0 WHERE d=6 AND a=6 AND c <> 6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge idx_a,idx_c,idx_d idx_a,idx_d 4,4 NULL # 100.00 Using intersect(idx_a,idx_d); Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`b` = 0 where ((`test`.`t1`.`a` = 6) and (`test`.`t1`.`d` = 6) and (`test`.`t1`.`c` <> 6))
|
|
SELECT COUNT(*), SUM(b) FROM t1 WHERE d=6 AND a=6 AND c <> 6;
|
|
COUNT(*) SUM(b)
|
|
0 NULL
|
|
UPDATE t1 SET b=0 WHERE d=6 AND a=6 AND c <> 6;
|
|
SELECT COUNT(*), SUM(b) FROM t1 WHERE d=6 AND a=6 AND c <> 6;
|
|
COUNT(*) SUM(b)
|
|
0 NULL
|
|
EXPLAIN UPDATE t1 SET a=100 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge idx_a,idx_c,idx_d idx_a,idx_d 4,4 NULL # 100.00 Using intersect(idx_a,idx_d); Using where; Using temporary
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`a` = 100 where ((`test`.`t1`.`a` = 6) and (`test`.`t1`.`d` = 6) and (`test`.`t1`.`c` in (1,2,3,4,5)))
|
|
SELECT COUNT(*), SUM(a) FROM t1 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
|
|
COUNT(*) SUM(a)
|
|
0 NULL
|
|
UPDATE t1 SET a=100 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
|
|
SELECT COUNT(*), SUM(a) FROM t1 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
|
|
COUNT(*) SUM(a)
|
|
0 NULL
|
|
EXPLAIN UPDATE t1 SET a=2 WHERE a=5 AND b=4 AND d<3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index_merge idx_a,idx_b,idx_d idx_a,idx_b 4,4 NULL # 100.00 Using intersect(idx_a,idx_b); Using where; Using temporary
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`a` = 2 where ((`test`.`t1`.`b` = 4) and (`test`.`t1`.`a` = 5) and (`test`.`t1`.`d` < 3))
|
|
UPDATE t1 SET a=2 WHERE a=5 AND b=4 AND d<3;
|
|
create table t2 (
|
|
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
|
|
a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
c INT NOT NULL,
|
|
d INT NOT NULL,
|
|
PRIMARY KEY pk (pk),
|
|
KEY idx_a (a),
|
|
KEY idx_b (b),
|
|
KEY idx_c (c),
|
|
KEY idx_d (d)
|
|
)engine=InnoDB;
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
EXPLAIN UPDATE t2 SET a=2 WHERE pk<2492 AND d<=1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t2 NULL range PRIMARY,idx_d PRIMARY 3 const # 100.00 Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t2` set `test`.`t2`.`a` = 2 where ((`test`.`t2`.`pk` < 2492) and (`test`.`t2`.`d` <= 1))
|
|
UPDATE t2 SET a=2 WHERE pk<2492 AND d<=1;
|
|
CREATE TABLE t3(
|
|
a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
c INT NOT NULL,
|
|
d INT NOT NULL,
|
|
e INT
|
|
);
|
|
INSERT INTO t3(a,b,c,d,e) VALUES (3890,3890,3890,3890,3890);
|
|
INSERT INTO t3(a,b,c,d,e) VALUES (4000,4000,4000,4000,4000);
|
|
EXPLAIN REPLACE INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 REPLACE t3 NULL ALL NULL NULL NULL NULL # NULL NULL
|
|
1 SIMPLE t1 NULL index_merge idx_b,idx_c idx_b,idx_c 4,4 NULL # 100.00 Using intersect(idx_b,idx_c); Using where
|
|
Warnings:
|
|
Note 1003 replace into `test`.`t3` /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where ((`test`.`t1`.`c` = 6) and (`test`.`t1`.`b` = 6))
|
|
SELECT COUNT(*) FROM t3;
|
|
COUNT(*)
|
|
2
|
|
REPLACE INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6;
|
|
SELECT COUNT(*) FROM t3;
|
|
COUNT(*)
|
|
17
|
|
EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t3 NULL ALL NULL NULL NULL NULL # NULL NULL
|
|
1 SIMPLE t1 NULL index_merge idx_b,idx_d idx_b,idx_d 4,4 NULL # 100.00 Using intersect(idx_b,idx_d); Using where
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t3` /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where ((`test`.`t1`.`b` = 6) and (`test`.`t1`.`d` = 6))
|
|
SELECT COUNT(*) FROM t3;
|
|
COUNT(*)
|
|
17
|
|
INSERT INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
|
|
SELECT COUNT(*) FROM t3;
|
|
COUNT(*)
|
|
32
|
|
CREATE TABLE t4 (
|
|
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
|
|
a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
c INT NOT NULL,
|
|
d INT NOT NULL,
|
|
PRIMARY KEY pk (pk),
|
|
KEY idx_cd (c,d),
|
|
KEY idx_bd (b,d)
|
|
);
|
|
INSERT INTO t4 SELECT * FROM t1;
|
|
ANALYZE TABLE t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t4 analyze status OK
|
|
EXPLAIN UPDATE t4 SET a=2 WHERE b=6 AND c=6 AND d=6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t4 NULL index_merge idx_xd,idx_xd idx_xd,idx_xd 8,8 NULL # 100.00 Using intersect(idx_xd,idx_xd); Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t4` set `test`.`t4`.`a` = 2 where ((`test`.`t4`.`d` = 6) and (`test`.`t4`.`c` = 6) and (`test`.`t4`.`b` = 6))
|
|
SELECT COUNT(*), SUM(a) FROM t4 WHERE b=6 AND c=6 AND d=6;
|
|
COUNT(*) SUM(a)
|
|
15 30
|
|
UPDATE t4 SET a=2 WHERE b=6 AND c=6 AND d=6;
|
|
SELECT COUNT(*), SUM(a) FROM t4 WHERE b=6 AND c=6 AND d=6;
|
|
COUNT(*) SUM(a)
|
|
15 30
|
|
DROP TABLE t1,t2,t3,t4;
|
|
#
|
|
# Bug#23855449 WL7093:INNODB: ASSERTION FAILURE:
|
|
# PREBUILT->MYSQL_PREFIX_LEN <= RECORD_BUFFER->R
|
|
#
|
|
CREATE TABLE t(x INT, y INT, pk INT PRIMARY KEY, KEY(x), KEY(y));
|
|
INSERT INTO t VALUES (1,1,1), (3,3,2), (3,3,3), (4,4,4), (3,3,5), (4,4,6),
|
|
(3,3,7), (8,8,8), (1,1,9), (3,3,10), (3,3,11), (4,4,12),
|
|
(3,3,13), (4,4,14), (3,3,15), (8,8,16);
|
|
ANALYZE TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t analyze status OK
|
|
EXPLAIN SELECT COUNT(*) FROM t WHERE x = 3 AND y = 4;;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL index_merge x,y y,x 5,5 NULL 2 100.00 Using intersect(y,x); Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` where ((`test`.`t`.`y` = 4) and (`test`.`t`.`x` = 3))
|
|
SELECT COUNT(*) FROM t WHERE x = 3 AND y = 4;;
|
|
COUNT(*)
|
|
0
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#24829050 INDEX_MERGE_INTERSECTION OPTIMIZATION CAUSES
|
|
# WRONG QUERY RESULTS
|
|
#
|
|
CREATE TABLE t1 (ID int, PRIMARY KEY (ID));
|
|
CREATE TABLE t2 (ID int, c1 int DEFAULT NULL, c2 int, PRIMARY KEY (ID),
|
|
KEY c1_INDEX (c1), KEY c2_INDEX (c2));
|
|
insert into t1 values (1);
|
|
insert into t2 values (1,1,20),(2,1,20),(3,1,30),(4,2,30);
|
|
SELECT * FROM t1 WHERE t1.ID=1 AND EXISTS (
|
|
SELECT 1 FROM t2 WHERE t2.c2=30 AND t2.c1=t1.ID);
|
|
ID
|
|
1
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|