CREATE TABLE t1(
f0 int auto_increment PRIMARY KEY,
f1 int,
f2 varchar(200)
) CHARSET=latin1;
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
create table t2
select * from information_schema.SESSION_STATUS
where
variable_name like 'handler_read_rnd%' or
variable_name like 'Sort%';
select * from t2 where variable_name like 'Sort%';
VARIABLE_NAME VARIABLE_VALUE
SORT_MERGE_PASSES 0
SORT_PRIORITY_QUEUE_SORTS 1
SORT_RANGE 0
SORT_ROWS 100
SORT_SCAN 1
select * from t2 where variable_name='HANDLER_READ_RND';
VARIABLE_NAME VARIABLE_VALUE
HANDLER_READ_RND 100
select
if(variable_value in (87701, 87802), 'OK', 'FAIL') as RES
from t2 where variable_name='HANDLER_READ_RND_NEXT';
RES
OK
DROP TABLE t1, tmp, t2;