CREATE TABLE t1(
f0 int auto_increment PRIMARY KEY,
f1 int,
f2 varchar(200)
);
INSERT INTO t1(f1, f2) VALUES 
(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
(96,"96"),(97,"97"),(98,"98"),(99,"99");
CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20));
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1,f2 FROM t1;
INSERT INTO t1(f1,f2) SELECT * FROM tmp;
select count(*) from t1;
count(*)
87700
set sort_buffer_size= 32768;
FLUSH STATUS;
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	0
Sort_scan	0
explain SELECT * FROM t1 ORDER BY f2 LIMIT 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	87700	Using filesort
SELECT * FROM t1 ORDER BY f2 LIMIT 100;
f0	f1	f2
1	0	0
101	0	0
201	0	0
301	0	0
401	0	0
501	0	0
601	0	0
701	0	0
801	0	0
901	0	0
1001	0	0
1101	0	0
1201	0	0
1301	0	0
1401	0	0
1501	0	0
1601	0	0
1701	0	0
1801	0	0
1901	0	0
2001	0	0
2101	0	0
2201	0	0
2301	0	0
2401	0	0
2501	0	0
2601	0	0
2701	0	0
2801	0	0
2901	0	0
3001	0	0
3101	0	0
3201	0	0
3301	0	0
3401	0	0
3501	0	0
3601	0	0
3701	0	0
3801	0	0
3901	0	0
4001	0	0
4101	0	0
4201	0	0
4301	0	0
4401	0	0
4501	0	0
4601	0	0
4701	0	0
4801	0	0
4901	0	0
5001	0	0
5101	0	0
5201	0	0
5301	0	0
5401	0	0
5501	0	0
5601	0	0
5701	0	0
5801	0	0
5901	0	0
6001	0	0
6101	0	0
6201	0	0
6301	0	0
6401	0	0
6501	0	0
6601	0	0
6701	0	0
6801	0	0
6901	0	0
7001	0	0
7101	0	0
7201	0	0
7301	0	0
7401	0	0
7501	0	0
7601	0	0
7701	0	0
7801	0	0
7901	0	0
8001	0	0
8101	0	0
8201	0	0
8301	0	0
8401	0	0
8501	0	0
8601	0	0
8701	0	0
8801	0	0
8901	0	0
9001	0	0
9101	0	0
9201	0	0
9301	0	0
9401	0	0
9501	0	0
9601	0	0
9701	0	0
9801	0	0
9901	0	0
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	100
Sort_scan	1
SHOW STATUS LIKE 'Handler_read_rnd%';
Variable_name	Value
Handler_read_rnd	100
Handler_read_rnd_deleted	0
Handler_read_rnd_next	87701
DROP TABLE t1, tmp;