You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
438 lines
14 KiB
438 lines
14 KiB
SET SQL_WARNINGS=1;
|
|
CREATE TABLE t1 (
|
|
a tinytext NOT NULL,
|
|
b tinyint(3) unsigned NOT NULL default '0',
|
|
PRIMARY KEY (a(32),b)
|
|
) ENGINE=MyISAM;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES ('a',1),('a',2);
|
|
SELECT * FROM t1 WHERE a='a' AND b=2;
|
|
a b
|
|
a 2
|
|
SELECT * FROM t1 WHERE a='a' AND b in (2);
|
|
a b
|
|
a 2
|
|
SELECT * FROM t1 WHERE a='a' AND b in (1,2);
|
|
a b
|
|
a 1
|
|
a 2
|
|
drop table t1;
|
|
CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT,
|
|
UNIQUE (c,i)) ENGINE=MYISAM;
|
|
INSERT IGNORE INTO t1 (c) VALUES (NULL),(NULL);
|
|
Warnings:
|
|
Warning 1048 Column 'c' cannot be null
|
|
SELECT * FROM t1;
|
|
c i
|
|
1
|
|
2
|
|
INSERT INTO t1 (c) VALUES ('a'),('a');
|
|
SELECT * FROM t1;
|
|
c i
|
|
1
|
|
2
|
|
a 1
|
|
a 2
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT,
|
|
UNIQUE (c,i)) ENGINE=MYISAM;
|
|
INSERT INTO t1 (c) VALUES (NULL),(NULL);
|
|
SELECT * FROM t1;
|
|
c i
|
|
NULL 1
|
|
NULL 2
|
|
INSERT INTO t1 (c) VALUES ('a'),('a');
|
|
SELECT * FROM t1;
|
|
c i
|
|
NULL 1
|
|
NULL 2
|
|
a 1
|
|
a 2
|
|
drop table t1;
|
|
CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam;
|
|
insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');
|
|
explain select 1 from t1 where id =2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
|
|
explain select 1 from t1 where id =2 or id=3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`id` = 2) or (`test`.`t1`.`id` = 3))
|
|
explain select name from t1 where id =2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 'b' AS `name` from `test`.`t1` where true
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);
|
|
explain select 1 from t1 where id =2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref id id 4 const 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`id` = 2)
|
|
drop table t1;
|
|
create table t1 (c varchar(30), t text, unique (c(2)), unique (t(3))) charset utf8 engine=myisam;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c` varchar(30) DEFAULT NULL,
|
|
`t` text,
|
|
UNIQUE KEY `c` (`c`(2)),
|
|
UNIQUE KEY `t` (`t`(3))
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8
|
|
insert t1 values ('cccc', 'tttt'),
|
|
(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1),
|
|
(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1);
|
|
insert t1 (c) values ('cc22');
|
|
ERROR 23000: Duplicate entry 'cc' for key 'c'
|
|
insert t1 (t) values ('ttt22');
|
|
ERROR 23000: Duplicate entry 'ttt' for key 't'
|
|
insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1);
|
|
ERROR 23000: Duplicate entry 'б!' for key 'c'
|
|
insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);
|
|
ERROR 23000: Duplicate entry 'бб!' for key 't'
|
|
select c from t1 where c='cccc';
|
|
c
|
|
cccc
|
|
select t from t1 where t='tttt';
|
|
t
|
|
tttt
|
|
select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1;
|
|
c
|
|
б!"#ббббб
|
|
select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1;
|
|
t
|
|
бб!"#бббб
|
|
drop table t1;
|
|
DROP TABLE IF EXISTS t1;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t1'
|
|
CREATE TABLE t1 (
|
|
c1 int,
|
|
c2 varbinary(240),
|
|
UNIQUE KEY (c1),
|
|
KEY (c2)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z');
|
|
INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z');
|
|
INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z');
|
|
select c1 from t1 where c2='\Z\Z\Z\Z';
|
|
c1
|
|
1
|
|
3
|
|
DELETE FROM t1 WHERE (c1 = 1);
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
select c1 from t1 where c2='\Z\Z\Z\Z';
|
|
c1
|
|
3
|
|
DELETE FROM t1 WHERE (c1 = 3);
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
select c1 from t1 where c2='\Z\Z\Z\Z';
|
|
c1
|
|
truncate table t1;
|
|
insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc");
|
|
delete from t1 where c1=3;
|
|
delete from t1 where c1=1;
|
|
delete from t1 where c1=4;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
create table t1 (
|
|
c1 int,
|
|
c2 varchar(20) not null,
|
|
primary key (c1),
|
|
key (c2(10))
|
|
) engine=myisam;
|
|
insert into t1 values (1,'');
|
|
insert into t1 values (2,' \t\tTest String');
|
|
insert into t1 values (3,' \n\tTest String');
|
|
update t1 set c2 = 'New Test String' where c1 = 1;
|
|
select * from t1;
|
|
c1 c2
|
|
1 New Test String
|
|
2 Test String
|
|
3
|
|
Test String
|
|
drop table t1;
|
|
CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES( 1 );
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL system NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select max('1') AS `MAX(a)` from dual
|
|
drop table t1;
|
|
CREATE TABLE t1 (a INT, b INT, INDEX (a,b)) engine=myisam;
|
|
INSERT INTO t1 (a, b)
|
|
VALUES
|
|
(1,1), (1,2), (1,3), (1,4), (1,5),
|
|
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6),
|
|
(5,1), (5,2), (5,3), (5,4), (5,5);
|
|
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
|
|
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
2 SUBQUERY t1 NULL range a a 5 NULL 11 100.00 Using index for group-by
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` `t1_outer` where false
|
|
SELECT 1 as RES FROM t1 AS t1_outer WHERE
|
|
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
|
|
RES
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#18144: Cost with FORCE/USE index seems incorrect in some cases.
|
|
#
|
|
# We are interested in showing that the cost for the last plan is higher
|
|
# than for the preceding two plans.
|
|
#
|
|
CREATE TABLE t1( a INT, b INT, KEY( a ) ) engine=myisam;
|
|
INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5);
|
|
EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index a a 5 NULL 6 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,sum(`test`.`t1`.`b`) AS `SUM( b )` from `test`.`t1` group by `test`.`t1`.`a`
|
|
SHOW STATUS LIKE 'Last_query_cost';
|
|
Variable_name Value
|
|
Last_query_cost 1.102296
|
|
EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index a a 5 NULL 6 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,sum(`test`.`t1`.`b`) AS `SUM( b )` from `test`.`t1` USE INDEX (`a`) group by `test`.`t1`.`a`
|
|
SHOW STATUS LIKE 'Last_query_cost';
|
|
Variable_name Value
|
|
Last_query_cost 1.102296
|
|
EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index a a 5 NULL 6 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,sum(`test`.`t1`.`b`) AS `SUM( b )` from `test`.`t1` FORCE INDEX (`a`) group by `test`.`t1`.`a`
|
|
SHOW STATUS LIKE 'Last_query_cost';
|
|
Variable_name Value
|
|
Last_query_cost 2.349000
|
|
DROP TABLE t1;
|
|
#
|
|
# Additional coverage for handling of key algorithm in index definition.
|
|
#
|
|
#
|
|
# 1) Default case. If key algorithm was not specified explicitly and
|
|
# this is normal key (not spatial or fulltext) storage engine
|
|
# default key algorithm is used.
|
|
#
|
|
create table tm (k int, index (k)) charset utf8mb4 engine=myisam;
|
|
#
|
|
# Key algorithm is not shown in SHOW CREATE TABLE output in this case
|
|
#
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#
|
|
# But visible in I_S.STATISTICS
|
|
#
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name ='tm' order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm BTREE
|
|
#
|
|
# Same applies when keys are added by ALTER TABLE
|
|
#
|
|
alter table tm add column l int, add index (l);
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`k` int(11) DEFAULT NULL,
|
|
`l` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`),
|
|
KEY `l` (`l`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' and index_name = 'l'
|
|
order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm BTREE
|
|
drop tables tm;
|
|
#
|
|
# 2) For SPATIAL and FULLTEXT keys we always use special key
|
|
# algorithms and there is no way to specify it explicitly.
|
|
#
|
|
create table tm (pk int primary key, p point not null SRID 0, spatial index (p))
|
|
charset utf8mb4 engine=myisam;
|
|
#
|
|
# Key algorithm is not shown in SHOW CREATE TABLE output in this
|
|
# case as well.
|
|
#
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`pk` int(11) NOT NULL,
|
|
`p` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`pk`),
|
|
SPATIAL KEY `p` (`p`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#
|
|
# But visible in I_S.STATISTICS
|
|
#
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' and index_name = 'p'
|
|
order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm SPATIAL
|
|
#
|
|
# Same applies when keys are added by ALTER TABLE
|
|
#
|
|
alter table tm add column q point not null SRID 0, add spatial index (q);
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' and index_name = 'q'
|
|
order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm SPATIAL
|
|
drop tables tm;
|
|
#
|
|
# Same applies to FULLTEXT indexes.
|
|
#
|
|
create table tm (pk int primary key, v varchar(255), fulltext index (v))
|
|
charset utf8mb4 engine=myisam;
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`pk` int(11) NOT NULL,
|
|
`v` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`),
|
|
FULLTEXT KEY `v` (`v`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' and index_name = 'v'
|
|
order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm FULLTEXT
|
|
#
|
|
# Same applies when keys are added by ALTER TABLE
|
|
#
|
|
alter table tm add column w varchar(255), add fulltext index (w);
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`pk` int(11) NOT NULL,
|
|
`v` varchar(255) DEFAULT NULL,
|
|
`w` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`),
|
|
FULLTEXT KEY `v` (`v`),
|
|
FULLTEXT KEY `w` (`w`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' and index_name = 'w'
|
|
order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm FULLTEXT
|
|
drop tables tm;
|
|
#
|
|
# 3) If key algorithm was specified explicitly and it is supported
|
|
# by the storage engine it will be used.
|
|
#
|
|
create table tm (k int, index using btree (k)) charset utf8mb4 engine=myisam;
|
|
#
|
|
# In this case it is shown in SHOW CREATE TABLE output
|
|
#
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`) USING BTREE
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#
|
|
# Also visible in I_S.STATISTICS
|
|
#
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm BTREE
|
|
#
|
|
# Same applies when keys are added by ALTER TABLE
|
|
#
|
|
# Note that the information that algorithm was provided explicitly is
|
|
# preserved by ALTER TABLE.
|
|
alter table tm add column l int, add index using btree (l);
|
|
show create table tm;
|
|
Table Create Table
|
|
tm CREATE TABLE `tm` (
|
|
`k` int(11) DEFAULT NULL,
|
|
`l` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`) USING BTREE,
|
|
KEY `l` (`l`) USING BTREE
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
select table_name, index_type from information_schema.statistics
|
|
where table_schema = 'test' and table_name = 'tm' and index_name = 'l'
|
|
order by table_name;
|
|
TABLE_NAME INDEX_TYPE
|
|
tm BTREE
|
|
drop tables tm;
|
|
#
|
|
# 7) Key algorithm and ALTER TABLE ALGORITHM=INPLACE
|
|
#
|
|
# Changing key algorithm from one used by default to the same one
|
|
# but specified explicitly should be inplace/fast operation.
|
|
create table t1 (k int, index (k)) charset utf8mb4 engine=myisam;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1 drop key k, add index using btree (k), algorithm=inplace;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`) USING BTREE
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1 drop key k, add index (k), algorithm=inplace;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
# Note that below statement doesn't really change algorithm.
|
|
alter table t1 drop key k, add index using hash (k), algorithm=inplace;
|
|
Warnings:
|
|
Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
|
|
drop table t1;
|
|
# Changing key algorithm to another one can require COPY algorithm.
|
|
create table t1 (k int, index (k)) charset utf8mb4 engine=heap;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1 drop key k, add index using btree (k), algorithm=inplace;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1 drop key k, add index using btree (k), algorithm=copy;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
KEY `k` (`k`) USING BTREE
|
|
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
|