rm -rf 93586/ mkdir -p /dev/shm/tmp scripts/mysql_install_db --basedir=$PWD --datadir=$PWD/93586 -v bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/93586 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/93586/log.err 2>&1 & rm -rf 93586 mkdir -p /dev/shm/tmp bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/93586 --log-error-verbosity=3 bin/mysqld --basedir=$PWD --datadir=$PWD/93586 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/93586/log.err --tmpdir=/dev/shm/tmp 2>&1 & create database if not exists test; use test; drop table if exists foo; CREATE TABLE `foo` ( `a` varchar(18) DEFAULT NULL, `b` text, `c` int(3) DEFAULT NULL, `d` varchar(60) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `foo` VALUES ('ALD-A/200808181115','',5,'******************************'),('ALD-A/200808181115','*****',10,'Start time'),('ALD-A/200808181115','*****',15,'********'),('ALD-A/200808181115','**************',20,'*******'),('ALD-A/200808181115','*',25,'********************'),('ALD-A/200808181115','*',30,'************************'),('ALD-A/200808181115','*',35,'*************************'),('ALD-A/200808181115','*',40,'**************'),('ALD-A/200808181115','*',45,'****************'),('ALD-A/200808181115','*',50,'**********************'); set @id:=50; insert into `foo`(`a`,`b`,`c`,`d`) select 'ALD-A/200808181115','',@id:=@id+5,'******************************' from `foo` k1, `foo` k2, `foo` k3, `foo` k4,`foo` k5,`foo` k6, `foo` k7, `foo` k8, `foo` k9, `foo` k0,`foo` ka, `foo` kb, `foo` kc, `foo` kd limit 300000; - 5.6/5.7 - remove collation part to avoid Unknown collation: 'utf8mb4_0900_ai_ci' -- 5.6.42 mysql> use test; Database changed mysql> drop table if exists foo; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE `foo` ( -> `a` varchar(18) DEFAULT NULL, -> `b` text, -> `c` int(3) DEFAULT NULL, -> `d` varchar(60) DEFAULT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `foo` VALUES ('ALD-A/200808181115','',5,'******************************'),('ALD-A/200808181115','*****',10,'Start time'),('ALD-A/200808181115','*****',15,'********'),('ALD-A/200808181115','**************',20,'*******'),('ALD-A/200808181115','*',25,'********************'),('ALD-A/200808181115','*',30,'************************'),('ALD-A/200808181115','*',35,'*************************'),('ALD-A/200808181115','*',40,'**************'),('ALD-A/200808181115','*',45,'****************'),('ALD-A/200808181115','*',50,'**********************'); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> set @id:=50; insert into `foo`(`a`,`b`,`c`,`d`) select 'ALD-A/200808181115','',@id:=@id+5,'******************************' from `foo` k1, `foo` k2, `foo` k3, `foo` k4,`fQuery OK, 0 rows affected (0.00 sec) oo` k5,`foo` k6, `foo` k7, `foo` k8, `foo` k9, `fomysql> o` k0,`insert into `foo`(`a`,`b`,`c`,`d`) select 'ALD-A/200808181115','',@id:=@id+5,'******************************' from `foo` k1, `foo` k2, `foo` k3, `foo` k4,`foo` k5,`foo` k6, `foo` k7, `foo` k8, `foo` k9, `foo` k0,`foo` ka, `foo` kb, `foo` kc, `foo` kd limit 300000; Query OK, 300000 rows affected (1.92 sec) Records: 300000 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1\G *************************** 1. row *************************** a: ALD-A/200808181115 b: 1 row in set (5.41 sec) mysql> explain format=json SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "table": { "table_name": "foo", "access_type": "ALL", "rows": 298544, "filtered": 100 } } } } } 1 row in set, 1 warning (0.03 sec) - 5.7.24 mysql> SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1\G *************************** 1. row *************************** a: ALD-A/200808181115 b: 1 row in set (5.19 sec) mysql> explain format=json SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "61344.80" }, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "table": { "table_name": "foo", "access_type": "ALL", "rows_examined_per_scan": 298544, "rows_produced_per_join": 298544, "filtered": "100.00", "cost_info": { "read_cost": "1636.00", "eval_cost": "59708.80", "prefix_cost": "61344.80", "data_read_per_join": "95M" }, "used_columns": [ "a", "b", "c", "d" ] } } } } } 1 row in set, 1 warning (0.01 sec) - 8.0.13 mysql> mysql> SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1\G *************************** 1. row *************************** a: ALD-A/200808181115 b: 1 row in set (2 min 21.13 sec) mysql> explain format=json SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "30263.40" }, "duplicates_removal": { "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "table": { "table_name": "foo", "access_type": "ALL", "rows_examined_per_scan": 298544, "rows_produced_per_join": 298544, "filtered": "100.00", "cost_info": { "read_cost": "409.00", "eval_cost": "29854.40", "prefix_cost": "30263.40", "data_read_per_join": "95M" }, "used_columns": [ "a", "b", "c", "d" ] } } } } } 1 row in set, 1 warning (0.00 sec) - session 2 mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+----------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+----------------------------------------------------------------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 152 | Waiting on empty queue | NULL | | 8 | root | localhost | test | Query | 110 | Removing duplicates | SELECT DISTINCT `a` ,`b` FROM `foo` GROUP BY `a` ,`b` ,`c` ,`d` limit 1 | | 9 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+----------------------------------------------------------------------------+ 3 rows in set (0.00 sec)