mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
845 lines
31 KiB
Text
845 lines
31 KiB
Text
SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';
|
|
set @innodb_stats_persistent_save= @@innodb_stats_persistent;
|
|
set @innodb_stats_persistent_sample_pages_save=
|
|
@@innodb_stats_persistent_sample_pages;
|
|
set global innodb_stats_persistent= 1;
|
|
set global innodb_stats_persistent_sample_pages=100;
|
|
CREATE DATABASE dbt3_s001;
|
|
use dbt3_s001;
|
|
explain
|
|
select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index
|
|
flush status;
|
|
select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
|
|
count(*)
|
|
1
|
|
show /*1*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 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
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select count(*) from lineitem use index(primary)
|
|
where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem const PRIMARY PRIMARY 8 const,const 1
|
|
flush status;
|
|
select count(*) from lineitem use index(primary)
|
|
where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
|
|
count(*)
|
|
1
|
|
show /*2*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select count(*) from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 1 Using where; Using index
|
|
flush status;
|
|
select count(*) from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
|
|
count(*)
|
|
1
|
|
show /*3*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 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
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select l_orderkey, l_linenumber from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 3 Using where; Using index
|
|
flush status;
|
|
select l_orderkey, l_linenumber from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
|
|
l_orderkey l_linenumber
|
|
1088 3
|
|
1217 1
|
|
1221 3
|
|
show /*4*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 3
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
|
flush status;
|
|
select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
|
|
min(l_orderkey)
|
|
130
|
|
show /*5*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select min(l_orderkey) from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
|
flush status;
|
|
select min(l_orderkey) from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
|
|
min(l_orderkey)
|
|
1088
|
|
show /*6*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select max(l_linenumber) from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey=130;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
|
flush status;
|
|
select max(l_linenumber) from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey=130;
|
|
max(l_linenumber)
|
|
2
|
|
show /*7*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select l_orderkey, l_linenumber
|
|
from lineitem use index (i_l_shipdate, i_l_receiptdate)
|
|
where l_shipdate='1992-07-01' and l_orderkey=130
|
|
or l_receiptdate='1992-07-01' and l_orderkey=5603;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 2 Using union(i_l_shipdate,i_l_receiptdate); Using where
|
|
flush status;
|
|
select l_orderkey, l_linenumber
|
|
from lineitem use index (i_l_shipdate, i_l_receiptdate)
|
|
where l_shipdate='1992-07-01' and l_orderkey=130
|
|
or l_receiptdate='1992-07-01' and l_orderkey=5603;
|
|
l_orderkey l_linenumber
|
|
130 2
|
|
5603 2
|
|
show /*8*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 2
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 2
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select l_orderkey, l_linenumber
|
|
from lineitem use index (i_l_shipdate, i_l_receiptdate)
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
|
|
or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate # NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where
|
|
flush status;
|
|
select l_orderkey, l_linenumber
|
|
from lineitem use index (i_l_shipdate, i_l_receiptdate)
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
|
|
or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
|
|
l_orderkey l_linenumber
|
|
130 2
|
|
5603 2
|
|
5959 3
|
|
show /*9*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 3
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 3
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select l_orderkey, l_linenumber from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
|
|
or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,i_l_receiptdate # NULL # Using
|
|
flush status;
|
|
select l_orderkey, l_linenumber from lineitem
|
|
where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
|
|
or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
|
|
l_orderkey l_linenumber
|
|
130 2
|
|
5603 2
|
|
5959 3
|
|
show /*a*/ status like 'handler_read_next';
|
|
Variable_name Value
|
|
Handler_read_next 3
|
|
explain
|
|
select max(l_orderkey) from lineitem
|
|
where l_partkey between 1 and 10 group by l_partkey;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index for group-by
|
|
flush status;
|
|
select max(l_orderkey) from lineitem
|
|
where l_partkey between 1 and 10 group by l_partkey;
|
|
max(l_orderkey)
|
|
5984
|
|
5957
|
|
5892
|
|
5856
|
|
5959
|
|
5957
|
|
5794
|
|
5894
|
|
5859
|
|
5632
|
|
show /*b*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 21
|
|
Handler_read_last 1
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
explain
|
|
select max(l_orderkey) from lineitem
|
|
where l_suppkey in (1,4) group by l_suppkey;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index for group-by
|
|
flush status;
|
|
select max(l_orderkey) from lineitem
|
|
where l_suppkey in (1,4) group by l_suppkey;
|
|
max(l_orderkey)
|
|
5988
|
|
5984
|
|
show /*c*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 6
|
|
Handler_read_last 1
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
create index i_p_retailprice on part(p_retailprice);
|
|
explain
|
|
select o_orderkey, p_partkey
|
|
from part use index (i_p_retailprice),
|
|
lineitem use index (i_l_partkey), orders
|
|
where p_retailprice > 1100 and o_orderdate='1997-01-01'
|
|
and o_orderkey=l_orderkey and p_partkey=l_partkey;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE part range i_p_retailprice i_p_retailprice 9 NULL # Using where; Using index
|
|
1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const # Using index
|
|
1 SIMPLE lineitem ref i_l_partkey i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey # Using index
|
|
flush status;
|
|
select o_orderkey, p_partkey
|
|
from part use index (i_p_retailprice),
|
|
lineitem use index (i_l_partkey), orders
|
|
where p_retailprice > 1100 and o_orderdate='1997-01-01'
|
|
and o_orderkey=l_orderkey and p_partkey=l_partkey;
|
|
o_orderkey p_partkey
|
|
5895 200
|
|
show /*d*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 3
|
|
Handler_read_last 0
|
|
Handler_read_next 3
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
#
|
|
# Bug mdev-3851: ref access used instead of expected eq_ref access
|
|
# when extended_keys=on
|
|
#
|
|
create table t0 (a int);
|
|
insert into t0 select seq from seq_1_to_5;
|
|
create index i_p_size on part(p_size);
|
|
explain
|
|
select straight_join * from t0, part ignore index (primary)
|
|
where p_partkey=t0.a and p_size=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t0 ALL NULL NULL NULL NULL 5 Using where
|
|
1 SIMPLE part eq_ref i_p_size i_p_size 9 const,dbt3_s001.t0.a 1
|
|
select straight_join * from t0, part ignore index (primary)
|
|
where p_partkey=t0.a and p_size=1;
|
|
a p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment
|
|
2 2 blush rosy metallic lemon navajo Manufacturer#1 Brand#13 LARGE BRUSHED BRASS 1 LG CASE 902 final platelets hang f
|
|
drop table t0;
|
|
drop index i_p_size on part;
|
|
DROP DATABASE dbt3_s001;
|
|
use test;
|
|
#
|
|
# LP Bug #914560: query containing IN subquery
|
|
# + extended_keys = on
|
|
#
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
SET optimizer_switch='materialization=on,semijoin=on';
|
|
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1,1), (2,2);
|
|
SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2);
|
|
a b
|
|
1 1
|
|
2 2
|
|
EXPLAIN
|
|
SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
|
|
1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1
|
|
2 MATERIALIZED s1 ALL NULL NULL NULL NULL 2
|
|
2 MATERIALIZED s2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
|
|
DROP TABLE t1;
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
#
|
|
# LP Bug #915291: query using a materialized view
|
|
# + extended_keys = on
|
|
# (valgrinf complains fixed by the patch for bug #914560)
|
|
#
|
|
SET optimizer_switch = 'derived_with_keys=on';
|
|
CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES ('j'), ('v');
|
|
CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM;
|
|
INSERT INTO t2 VALUES ('j'), ('v');
|
|
CREATE TABLE t3 (c varchar(1));
|
|
INSERT INTO t2 VALUES ('m'), ('n');
|
|
CREATE VIEW v
|
|
AS SELECT DISTINCT * FROM t2 STRAIGHT_JOIN t3;
|
|
SELECT * FROM t1, v WHERE a = b;
|
|
a b c
|
|
DROP VIEW v;
|
|
DROP TABLE t1,t2,t3;
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
#
|
|
# LP Bug #921167: query containing IN subquery
|
|
# + extended_keys = on
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int NOT NULL, b varchar(1) NOT NULL, KEY(a), KEY(b,a)
|
|
) ENGINE=MyISAM CHARSET=latin1;
|
|
INSERT INTO t1 VALUES
|
|
(0,'j'), (8,'v'), (1,'c'), (8,'m'), (9,'d'),
|
|
(24,'d'), (6,'y'), (1,'t'), (6,'d'), (2,'s');
|
|
CREATE TABLE t2 (
|
|
c int NOT NULL PRIMARY KEY
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t2 VALUES
|
|
(10), (11), (12), (13), (14),
|
|
(15), (16), (17), (18), (19), (24);
|
|
EXPLAIN
|
|
SELECT a FROM t1 AS t, t2 as t2_out
|
|
WHERE t2_out.c = t.a AND t.b IN (SELECT b FROM t1, t2 WHERE b = t.b);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 index b b 7 NULL 10 Using index; Start temporary
|
|
1 PRIMARY t ref a,b b 3 test.t1.b 2 Using index
|
|
1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t2_out eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index
|
|
SELECT a FROM t1 AS t, t2 as t2_out
|
|
WHERE t2_out.c = t.a AND t.b IN (SELECT b FROM t1, t2 WHERE b = t.b);
|
|
a
|
|
24
|
|
Last_query_cost 0.120558
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# LP Bug #923236: hash join + extended_keys = on
|
|
#
|
|
CREATE TABLE t1 (a int) ENGINE=MyISAM;
|
|
CREATE TABLE t2 (b int) ENGINE=MyISAM;
|
|
INSERT INTO t1 (a) VALUES (4), (6);
|
|
INSERT INTO t2 (b) VALUES (0), (8);
|
|
set @save_join_cache_level=@@join_cache_level;
|
|
SET join_cache_level=3;
|
|
SET optimizer_switch='join_cache_hashed=on';
|
|
SET optimizer_switch='join_cache_bka=on';
|
|
EXPLAIN
|
|
SELECT * FROM t1, t2 WHERE b=a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 2 Using where; Using join buffer (flat, BNLH join)
|
|
SELECT * FROM t1, t2 WHERE b=a;
|
|
a b
|
|
set join_cache_level=@save_join_cache_level;
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Bug mdev-3888: INSERT with UPDATE on duplicate keys
|
|
# with extended_keys=on
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
c2 bigint(20) unsigned NOT NULL,
|
|
c3 bigint(20) unsigned NOT NULL,
|
|
c4 varchar(128) DEFAULT NULL,
|
|
PRIMARY KEY (c1),
|
|
UNIQUE KEY uq (c2,c3),
|
|
KEY c3 (c3),
|
|
KEY c4 (c4)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
|
|
INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
|
|
ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
|
|
INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
|
|
ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug mdev-4220: using ref instead of eq_ref
|
|
# with extended_keys=on
|
|
# (performance regression introduced in the patch for mdev-3851)
|
|
#
|
|
create table t1 (a int not null) engine=innodb;
|
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t2 (
|
|
pk int primary key, a int not null, b int, unique(a)
|
|
)engine=innodb;
|
|
insert into t2
|
|
select
|
|
A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a
|
|
from t1 A, t1 B;
|
|
explain
|
|
select * from t1, t2 where t2.a=t1.a and t2.b < 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL #
|
|
1 SIMPLE t2 eq_ref a a 4 test.t1.a # Using where
|
|
flush status;
|
|
select * from t1, t2 where t2.a=t1.a and t2.b < 2;
|
|
a pk a b
|
|
0 0 0 0
|
|
1 1 1 1
|
|
show /*e*/ status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 10
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 11
|
|
drop table t1,t2;
|
|
create table t1(a int) engine=myisam;
|
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t2(a int) engine=myisam;
|
|
insert into t2 select A.a + 10*B.a + 100*C.a from t1 A, t1 B, t1 C;
|
|
create table t3 (
|
|
pk1 int not null, pk2 int not null, col1 int not null, col2 int not null)
|
|
engine=innodb;
|
|
insert into t3 select a,a,a,a from t2;
|
|
alter table t3 add primary key (pk1, pk2);
|
|
alter table t3 add key (col1, col2);
|
|
analyze table t1,t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status OK
|
|
explain
|
|
select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where
|
|
1 SIMPLE t3 ref col1 col1 8 test.t1.a,test.t1.a # Using index
|
|
explain
|
|
select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where
|
|
1 SIMPLE t3 ref PRIMARY,col1 col1 12 test.t1.a,test.t1.a,test.t1.a # Using index
|
|
drop table t1,t2,t3;
|
|
#
|
|
# Bug mdev-4340: performance regression with extended_keys=on
|
|
#
|
|
CREATE TABLE t1 (
|
|
page_id int(8) unsigned NOT NULL AUTO_INCREMENT,
|
|
page_namespace int(11) NOT NULL DEFAULT '0',
|
|
page_title varbinary(255) NOT NULL DEFAULT '',
|
|
page_restrictions tinyblob NOT NULL,
|
|
page_counter bigint(20) unsigned NOT NULL DEFAULT '0',
|
|
page_is_redirect tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
page_is_new tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
page_random double unsigned NOT NULL DEFAULT '0',
|
|
page_touched varbinary(14) NOT NULL DEFAULT '',
|
|
page_latest int(8) unsigned NOT NULL DEFAULT '0',
|
|
page_len int(8) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (page_id),
|
|
UNIQUE KEY name_title (page_namespace,page_title),
|
|
KEY page_random (page_random),
|
|
KEY page_len (page_len),
|
|
KEY page_redirect_namespace_len (page_is_redirect,page_namespace,page_len)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=38929100 DEFAULT CHARSET=binary;
|
|
INSERT INTO t1 VALUES
|
|
(38928077,0,'Sandbox','',0,0,0,0,'',0,0),(38928078,1,'Sandbox','',0,0,0,1,'',0,0),
|
|
(38928079,2,'Sandbox','',0,0,0,2,'',0,0),(38928080,3,'Sandbox','',0,0,0,3,'',0,0),
|
|
(38928081,4,'Sandbox','',0,0,0,4,'',0,0),(38928082,5,'Sandbox','',0,0,0,5,'',0,0);
|
|
CREATE TABLE t2 (
|
|
rev_id int(8) unsigned NOT NULL AUTO_INCREMENT,
|
|
rev_page int(8) unsigned NOT NULL DEFAULT '0',
|
|
rev_text_id int(8) unsigned NOT NULL DEFAULT '0',
|
|
rev_comment varbinary(255) DEFAULT NULL,
|
|
rev_user int(5) unsigned NOT NULL DEFAULT '0',
|
|
rev_user_text varbinary(255) NOT NULL DEFAULT '',
|
|
rev_timestamp varbinary(14) NOT NULL DEFAULT '',
|
|
rev_minor_edit tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
rev_deleted tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
rev_len int(8) unsigned DEFAULT NULL,
|
|
rev_parent_id int(8) unsigned DEFAULT NULL,
|
|
rev_sha1 varbinary(32) NOT NULL DEFAULT '',
|
|
PRIMARY KEY (rev_page,rev_id),
|
|
UNIQUE KEY rev_id (rev_id),
|
|
KEY rev_timestamp (rev_timestamp),
|
|
KEY page_timestamp (rev_page,rev_timestamp),
|
|
KEY user_timestamp (rev_user,rev_timestamp),
|
|
KEY usertext_timestamp (rev_user_text,rev_timestamp,rev_user,rev_deleted,rev_minor_edit,rev_text_id,rev_comment)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=binary;
|
|
INSERT INTO t2 VALUES
|
|
(547116222,20,0,NULL,3,'','',0,0,NULL,NULL,''),(547117245,20,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547118268,20,0,NULL,5,'','',0,0,NULL,NULL,''),(547114177,21,0,NULL,1,'','',0,0,NULL,NULL,''),
|
|
(547115200,21,0,NULL,2,'','',0,0,NULL,NULL,''),(547116223,21,0,NULL,3,'','',0,0,NULL,NULL,''),
|
|
(547117246,21,0,NULL,4,'','',0,0,NULL,NULL,''),(547118269,21,0,NULL,5,'','',0,0,NULL,NULL,''),
|
|
(547114178,22,0,NULL,1,'','',0,0,NULL,NULL,''),(547115201,22,0,NULL,2,'','',0,0,NULL,NULL,''),
|
|
(547116224,22,0,NULL,3,'','',0,0,NULL,NULL,''),(547117247,22,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547116226,24,0,NULL,3,'','',0,0,NULL,NULL,''),(547117249,24,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547118272,24,0,NULL,5,'','',0,0,NULL,NULL,''),(547114181,25,0,NULL,1,'','',0,0,NULL,NULL,''),
|
|
(547115204,25,0,NULL,2,'','',0,0,NULL,NULL,''),(547116227,25,0,NULL,3,'','',0,0,NULL,NULL,''),
|
|
(547116157,978,0,NULL,2,'','',0,0,NULL,NULL,''),(547117180,978,0,NULL,3,'','',0,0,NULL,NULL,''),
|
|
(547118203,978,0,NULL,4,'','',0,0,NULL,NULL,''),(547119226,978,0,NULL,5,'','',0,0,NULL,NULL,''),
|
|
(547115135,979,0,NULL,1,'','',0,0,NULL,NULL,''),(547116158,979,0,NULL,2,'','',0,0,NULL,NULL,''),
|
|
(547116173,994,0,NULL,2,'','',0,0,NULL,NULL,''),(547117196,994,0,NULL,3,'','',0,0,NULL,NULL,''),
|
|
(547118219,994,0,NULL,4,'','',0,0,NULL,NULL,''),(547119242,994,0,NULL,5,'','',0,0,NULL,NULL,''),
|
|
(547115151,995,0,NULL,1,'','',0,0,NULL,NULL,''),(547116174,995,0,NULL,2,'','',0,0,NULL,NULL,''),
|
|
(547117197,995,0,NULL,3,'','',0,0,NULL,NULL,''),(547118220,995,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547118223,998,0,NULL,4,'','',0,0,NULL,NULL,''),(547119246,998,0,NULL,5,'','',0,0,NULL,NULL,''),
|
|
(547115155,999,0,NULL,1,'','',0,0,NULL,NULL,''),(547116178,999,0,NULL,2,'','',0,0,NULL,NULL,''),
|
|
(547117201,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547118224,999,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547117213,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547117217,999,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547117214,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547117218,999,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547117215,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547117219,999,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547117216,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547117220,999,0,NULL,4,'','',0,0,NULL,NULL,''),
|
|
(547119271,38928081,0,NULL,10,'','',0,0,NULL,NULL,''),(547119272,38928081,0,NULL,11,'','',0,0,NULL,NULL,''),
|
|
(547119273,38928081,0,NULL,12,'','',0,0,NULL,NULL,''),(547119274,38928081,0,NULL,13,'','',0,0,NULL,NULL,''),
|
|
(547119275,38928081,0,NULL,14,'','',0,0,NULL,NULL,''),(547119276,38928081,0,NULL,15,'','',0,0,NULL,NULL,''),
|
|
(547119277,38928081,0,NULL,16,'','',0,0,NULL,NULL,''),(547119278,38928081,0,NULL,17,'','',0,0,NULL,NULL,''),
|
|
(547119279,38928081,0,NULL,18,'','',0,0,NULL,NULL,''),(547119280,38928081,0,NULL,19,'','',0,0,NULL,NULL,'');
|
|
CREATE TABLE t3 (
|
|
old_id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
old_text mediumblob NOT NULL,
|
|
old_flags tinyblob NOT NULL,
|
|
PRIMARY KEY (old_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t3 VALUES
|
|
(1,'text-0',''),(2,'text-1000',''),(3,'text-2000',''),(4,'text-3000',''),
|
|
(5,'text-4000',''),(6,'text-5000',''),(7,'text-6000',''),(8,'text-7000',''),
|
|
(9,'text-8000',''),(10,'text-9000',''),(11,'text-1',''),(12,'text-1001',''),
|
|
(13,'text-2001',''),(14,'text-3001',''),(15,'text-4001',''),(16,'text-5001',''),
|
|
(17,'text-6001',''),(18,'text-7001',''),(19,'text-8001',''),(20,'text-9001',''),
|
|
(21,'text-2',''),(22,'text-1002',''),(23,'text-2002',''),(24,'text-3002',''),
|
|
(25,'text-4002',''),(26,'text-5002',''),(27,'text-6002',''),(28,'text-7002',''),
|
|
(29,'text-8002',''),(30,'text-9002',''),(31,'text-3',''),(32,'text-1003',''),
|
|
(33,'text-2003',''),(34,'text-3003',''),(35,'text-4003',''),(36,'text-5003',''),
|
|
(37,'text-6003',''),(38,'text-7003',''),(39,'text-8003',''),(40,'text-9003',''),
|
|
(41,'text-4',''),(42,'text-1004',''),(43,'text-2004',''),(44,'text-3004',''),
|
|
(45,'text-4004',''),(46,'text-5004',''),(47,'text-6004',''),(48,'text-7004',''),
|
|
(49,'text-8004',''),(50,'text-9004',''),(51,'text-5',''),(52,'text-1005',''),
|
|
(53,'text-2005',''),(54,'text-3005',''),(55,'text-4005',''),(56,'text-5005',''),
|
|
(57,'text-6005',''),(58,'text-7005',''),(59,'text-8005',''),(60,'text-9005',''),
|
|
(61,'text-6',''),(62,'text-1006',''),(63,'text-2006',''),(64,'text-3006',''),
|
|
(65,'text-4006',''),(66,'text-5006',''),(67,'text-6006',''),(68,'text-7006',''),
|
|
(69,'text-8006',''),(70,'text-9006',''),(71,'text-7',''),(72,'text-1007',''),
|
|
(73,'text-2007',''),(74,'text-3007',''),(75,'text-4007',''),(76,'text-5007',''),
|
|
(77,'text-6007',''),(78,'text-7007',''),(79,'text-8007',''),(80,'text-9007',''),
|
|
(81,'text-8',''),(82,'text-1008',''),(83,'text-2008',''),(84,'text-3008',''),
|
|
(85,'text-4008',''),(86,'text-5008',''),(87,'text-6008',''),(88,'text-7008',''),
|
|
(89,'text-8008',''),(90,'text-9008',''),(91,'text-9',''),(92,'text-1009',''),
|
|
(93,'text-2009',''),(94,'text-3009',''),(95,'text-4009',''),(96,'text-5009',''),
|
|
(97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009','');
|
|
ANALYZE TABLE t1,t2,t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'page_restrictions'
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze Warning Engine-independent statistics are not collected for column 'old_text'
|
|
test.t3 analyze Warning Engine-independent statistics are not collected for column 'old_flags'
|
|
test.t3 analyze status OK
|
|
EXPLAIN
|
|
SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3
|
|
WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox'
|
|
ORDER BY rev_timestamp ASC LIMIT 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const PRIMARY,name_title name_title 261 const,const 1
|
|
1 SIMPLE t2 ref page_timestamp page_timestamp 4 const 10 Using where
|
|
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.rev_text_id 1
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected
|
|
# results (InnoDB/XtraDB)
|
|
#
|
|
create table t1 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = myisam default character set utf8;
|
|
create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8;
|
|
insert into t1 (b) values (null), (null), (null);
|
|
insert into t2 (b) values (null), (null), (null);
|
|
analyze table t1,t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
explain select a from t1 where b is null order by a desc limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index b PRIMARY 8 NULL 2 Using where
|
|
select a from t1 where b is null order by a desc limit 2;
|
|
a
|
|
3
|
|
2
|
|
explain select a from t2 where b is null order by a desc limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index b PRIMARY 8 NULL 2 Using where
|
|
select a from t2 where b is null order by a desc limit 2;
|
|
a
|
|
3
|
|
2
|
|
explain select a from t2 where b is null order by a desc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where
|
|
select a from t2 where b is null order by a desc;
|
|
a
|
|
3
|
|
2
|
|
1
|
|
explain select a from t2 where b is null order by a desc,a,a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where
|
|
select a from t2 where b is null order by a desc,a,a;
|
|
a
|
|
3
|
|
2
|
|
1
|
|
drop table t1, t2;
|
|
#
|
|
# MDEV-10325: Queries examines all rows of a tables when it should not
|
|
#
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (
|
|
pk int not null,
|
|
col1 varchar(32),
|
|
filler varchar(100),
|
|
key idx1(col1(10)),
|
|
primary key (pk)
|
|
)engine=innodb;
|
|
insert into t1
|
|
select
|
|
A.a + 10*B.a + 100*C.a,
|
|
concat('1234567890-', 1000+ A.a + 10*B.a + 100*C.a),
|
|
repeat('filler-data-', 4)
|
|
from
|
|
t0 A, t0 B, t0 C;
|
|
drop table t0,t1;
|
|
#
|
|
#
|
|
# MDEV-10360: Extended keys: index properties depend on index order
|
|
#
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (
|
|
index_id bigint(20) unsigned NOT NULL,
|
|
index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL ,
|
|
index_object_id int(10) unsigned NOT NULL DEFAULT '0' ,
|
|
index_date_updated int(10) unsigned DEFAULT NULL ,
|
|
PRIMARY KEY (index_id),
|
|
KEY object (index_class(181),index_object_id),
|
|
KEY index_date_updated (index_date_updated)
|
|
) engine=innodb;
|
|
create table t2 (
|
|
index_id bigint(20) unsigned NOT NULL,
|
|
index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL ,
|
|
index_object_id int(10) unsigned NOT NULL DEFAULT '0' ,
|
|
index_date_updated int(10) unsigned DEFAULT NULL ,
|
|
PRIMARY KEY (index_id),
|
|
KEY index_date_updated (index_date_updated),
|
|
KEY object (index_class(181),index_object_id)
|
|
) engine=innodb;
|
|
insert into t1 select
|
|
@a:=A.a + 10*B.a + 100*C.a,
|
|
concat('val-', @a),
|
|
123456,
|
|
A.a + 10*B.a
|
|
from
|
|
t0 A, t0 B, t0 C;
|
|
insert into t2 select * from t1;
|
|
# This must have the same query plan as the query below it:
|
|
# type=range, key=index_date_updated, key_len=13
|
|
explain
|
|
select * from t1 force index(index_date_updated)
|
|
where index_date_updated= 10 and index_id < 800;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range index_date_updated index_date_updated 13 NULL # Using index condition
|
|
# This used to work from the start:
|
|
explain
|
|
select * from t2 force index(index_date_updated)
|
|
where index_date_updated= 10 and index_id < 800;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 range index_date_updated index_date_updated 13 NULL # Using index condition
|
|
drop table t0,t1,t2;
|
|
#
|
|
# MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff'
|
|
# was corrupted, server crashes in opt_sum_query
|
|
SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity, @@optimizer_use_condition_selectivity=4;
|
|
CREATE TABLE t1 (
|
|
pk INT,
|
|
f1 VARCHAR(3),
|
|
f2 VARCHAR(1024),
|
|
PRIMARY KEY (pk),
|
|
KEY(f2)
|
|
) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC;
|
|
INSERT INTO t1 VALUES (1,'foo','abc'),(2,'bar','def');
|
|
SELECT MAX(t2.pk) FROM t1 t2 INNER JOIN t1 t3 ON t2.f1 = t3.f1 WHERE t2.pk <= 4;
|
|
MAX(t2.pk)
|
|
2
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
pk1 INT,
|
|
pk2 INT,
|
|
f1 VARCHAR(3),
|
|
f2 VARCHAR(1021),
|
|
PRIMARY KEY (pk1,pk2),
|
|
KEY(f2)
|
|
) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC;
|
|
INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def');
|
|
explain format=json
|
|
select * from t1 force index(f2) where pk1 <= 5 and pk2 <=5 and f2 = 'abc' and f1 <= '3';
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "range",
|
|
"possible_keys": ["f2"],
|
|
"key": "f2",
|
|
"key_length": "3070",
|
|
"used_key_parts": ["f2", "pk1"],
|
|
"loops": 1,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'",
|
|
"attached_condition": "t1.f1 <= '3'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
f2 INT,
|
|
pk2 INT,
|
|
f1 VARCHAR(3),
|
|
pk1 VARCHAR(1000),
|
|
PRIMARY KEY (pk1,pk2),
|
|
KEY k1(pk1,f2)
|
|
) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC;
|
|
INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def');
|
|
explain format=json
|
|
select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3';
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "range",
|
|
"possible_keys": ["k1"],
|
|
"key": "k1",
|
|
"key_length": "3011",
|
|
"used_key_parts": ["pk1", "f2", "pk2"],
|
|
"loops": 1,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'",
|
|
"attached_condition": "t1.f1 <= '3'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
drop table t1;
|
|
SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
|
|
#
|
|
# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
|
|
#
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
CREATE TABLE t2 (
|
|
pk VARCHAR(50),
|
|
a VARCHAR(20),
|
|
KEY k1(a),
|
|
PRIMARY KEY(pk)
|
|
)ENGINE=INNODB CHARSET=latin1;
|
|
INSERT INTO t2 SELECT a,a FROM t1;
|
|
EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL k1 23 NULL # Using index
|
|
DROP TABLE t1,t2;
|
|
set global innodb_stats_persistent= @innodb_stats_persistent_save;
|
|
set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save;
|