60 lines
1.7 KiB
Plaintext
60 lines
1.7 KiB
Plaintext
drop table if exists t1;
|
|
create table t1(
|
|
id int(11) NOT NULL,
|
|
c1 int(11) DEFAULT NULL,
|
|
c2 int(11) DEFAULT NULL,
|
|
PRIMARY KEY(id),
|
|
KEY idx_c1c2(c1, c2)
|
|
)ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
drop procedure if exists insert_data;
|
|
CREATE PROCEDURE insert_data(start int, end int)
|
|
BEGIN
|
|
DECLARE i int DEFAULT 0;
|
|
DECLARE a int DEFAULT 0;
|
|
DECLARE b int DEFAULT 0;
|
|
set i=start;
|
|
WHILE i<end
|
|
DO
|
|
SELECT FLOOR(RAND()*(1000000-0+1)+0) into a;
|
|
SELECT FLOOR(RAND()*(1000000-0+1)+0) into b;
|
|
INSERT INTO t1(id,c1,c2) VALUES(i,a,b);
|
|
set i=i+1;
|
|
END WHILE;
|
|
END//
|
|
call insert_data(0,5000);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
call insert_data(5000,10000);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
call insert_data(10000,15000);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
call insert_data(15000,20000);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
select count(*) from t1;
|
|
count(*)
|
|
20000
|
|
alter table t1 add index idx_c1_c2(c1,c2);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL,
|
|
`c1` int(11) DEFAULT NULL,
|
|
`c2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_c1c2` (`c1`,`c2`),
|
|
KEY `idx_c1_c2` (`c1`,`c2`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table if exists t1;
|
|
drop procedure if exists insert_data;
|