Default page

Your IP: 216.73.216.124
Server IP: 50.28.103.30
Server: Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software: nginx/1.28.0
PHP Version: 8.3.12
Buat File | Buat Folder
View File Name : wl6711_heap_to_disk.result

set @old_heap_size= @@max_heap_table_size;
set max_heap_table_size = 16384;
select * from information_schema.collations order by id limit 1;
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN
big5_chinese_ci	big5	1	Yes	Yes	1
set optimizer_switch='firstmatch=off';
set optimizer_switch='loosescan=off';
drop table if exists t1;
Warnings:
Note	1051	Unknown table 'test.t1'
create table t1 (
a int not null,
b int not null,
c datetime default null
);
insert into t1 values 
(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
(406994,67,'2006-02-27 11:26:46'), (256,67,null),
(398341,67,'2006-02-20 04:48:44'), (254,67,null),(1120,67,null),
(406988,67,'2006-02-23 17:07:22'), (255,67,null),
(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
(154504,67,'2005-10-28 11:53:01'),(9199,67,null),(1,67,'2006-02-23 15:01:35'),
(223456,67,null),(4101,67,null),(1133,67,null),
(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27');
insert into t1 select a+10, b+10, c from t1;
insert into t1 select a+20, b+20, c from t1;
insert into t1 select a+30, b+30, c from t1;
insert into t1 select a+40, b+40, c from t1;
insert into t1 select a+50, b+50, c from t1;
insert into t1 select a+60, b+60, c from t1;
insert into t1 select a+70, b+70, c from t1;
insert into t1 select a+80, b+80, c from t1;
drop table if exists t11;
Warnings:
Note	1051	Unknown table 'test.t11'
create table t11 select * from t1;
insert into t1 select a+80, b+80, c from t1;
explain select * from t1 where a in (select a from t11) order by 1, 2, 3 limit 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	Using where; Using filesort
1	SIMPLE	<subquery2>	NULL	eq_ref	<auto_key>	<auto_key>	4	test.t1.a	x	100.00	NULL
2	MATERIALIZED	t11	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` semi join (`test`.`t11`) where (`<subquery2>`.`a` = `test`.`t1`.`a`) order by `test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c` limit 1
select * from t1 where a in (select a from t11) order by 1, 2, 3 limit 1;
a	b	c
1	67	2006-02-23 15:01:35
set optimizer_switch='materialization=off';
explain select * from t1 where a in (select a from t11) order by 1, 2, 3 limit 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t11	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	Using temporary; Using filesort; Start temporary
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	x	10.00	Using where; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t1`.`a` = `test`.`t11`.`a`) order by `test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c` limit 1
select * from t1 where a in (select a from t11) order by 1, 2, 3 limit 1;
a	b	c
1	67	2006-02-23 15:01:35
drop table t11;
set optimizer_switch=default;
drop table if exists t1, t2;
Warnings:
Note	1051	Unknown table 'test.t2'
CREATE TABLE t1 (id INTEGER);
CREATE TABLE t2 (id INTEGER);
INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
INSERT INTO t1 (id) SELECT id FROM t1;
/* 8 */
INSERT INTO t1 (id) SELECT id FROM t1;
/* 12 */
INSERT INTO t1 (id) SELECT id FROM t1;
/* 16 */
INSERT INTO t1 (id) SELECT id FROM t1;
/* 20 */
INSERT INTO t1 (id) SELECT id FROM t1;
/* 24 */
INSERT INTO t1 SELECT id+1 FROM t1;
INSERT INTO t1 SELECT id+2 FROM t1;
INSERT INTO t1 SELECT id+4 FROM t1;
INSERT INTO t1 SELECT id+8 FROM t1;
INSERT INTO t1 SELECT id+16 FROM t1;
INSERT INTO t1 SELECT id+32 FROM t1;
INSERT INTO t1 SELECT id+64 FROM t1;
INSERT INTO t1 SELECT id+128 FROM t1;
INSERT INTO t1 SELECT id+256 FROM t1;
INSERT INTO t1 SELECT id+512 FROM t1;
INSERT INTO t1 SELECT id+1024 FROM t1;
INSERT INTO t2 SELECT id + 2000 FROM t1 limit 4000;
explain select * from t1 union select * from t2 order by 1 limit 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	NULL
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	NULL
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	x	NULL	Using temporary; Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` order by `id` limit 1
select * from t1 union select * from t2 order by 1 limit 1;
id
1
explain SELECT SUM(id) sm FROM t1 group by id order by sm limit 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	Using temporary; Using filesort
Warnings:
Note	1003	/* select#1 */ select sum(`test`.`t1`.`id`) AS `sm` from `test`.`t1` group by `test`.`t1`.`id` order by `sm` limit 1
SELECT SUM(id) sm FROM t1 group by id order by sm limit 1;
sm
128
explain SELECT SUM(DISTINCT t1.id) sm FROM t1 left join t2 on t1.id=t2.id GROUP BY t1.id order by sm limit 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	Using temporary; Using filesort
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	x	100.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select sum(distinct `test`.`t1`.`id`) AS `sm` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`id` = `test`.`t1`.`id`)) where 1 group by `test`.`t1`.`id` order by `sm` limit 1
SELECT SUM(DISTINCT t1.id) sm FROM t1 left join t2 on t1.id=t2.id GROUP BY t1.id order by sm limit 1;
sm
1
set max_heap_table_size = @old_heap_size;
drop table t1;
drop table t2;
#