Performance Comparison between distinct and groupby in MySQL _ MySQL

This article mainly compares the performance of distinct and groupby in MySQL. the content is more detailed and the comparison results can be seen intuitively, if you are interested, you can refer to some tests you have seen on the Internet recently. this is not very accurate. I personally tested it today. It is concluded that the testing process may not be comprehensive enough for reference only.

Test process:
Prepare a test table

CREATE TABLE `test_test` (  `id` int(11) NOT NULL auto_increment,  `num` int(11) NOT NULL default '0',  PRIMARY KEY (`id`)  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Insert 10 million pieces of data into the table during the creation of a storage process

 create procedure p_test(pa int(11))   begin     declare max_num int(11) default 100000;   declare i int default 0;   declare rand_num int;     select count(id) into max_num from test_test;     while i < pa do     if max_num < 100000 then       select cast(rand()*100 as unsigned) into rand_num;       insert into test_test(num)values(rand_num);     end if;     set i = i +1;   end while;   end

Call a stored procedure to insert data

call p_test(100000);

Start Testing: (without indexing)

Select distinct num from test_test; select num from test_test group by num; [SQL] select distinct num from test_test; affected rows: 0 Time: 0.078 ms [SQL] select num from test_test group by num; affected rows: 0 Time: 0.031 ms

2. create an index on the num field

ALTER TABLE `test_test` ADD INDEX `num_index` (`num`) ;

Query again

Select distinct num from test_test; select num from test_test group by num; [SQL] select distinct num from test_test; affected rows: 0 Time: 0.000 ms [SQL] select num from test_test group by num; affected rows: 0 Time: 0.000 ms

At this time, we found that the time was too small to be accurate for 0.000 seconds.
Go to the command line to test

 mysql> set profiling=1; mysql> select distinct(num) from test_test;  mysql> select num from test_test group by num; mysql> show profiles; +----------+------------+----------------------------------------+ | Query_ID | Duration | Query         | +----------+------------+----------------------------------------+ |  1 | 0.00072550 | select distinct(num) from test_test | |  2 | 0.00071650 | select num from test_test group by num | +----------+------------+----------------------------------------+

Analysis:
After the index is addedDistinct is 107 times faster than distinct with no index added.
After the index is addedGroup by is 43 times faster than group by with no index added.
Comparison:Distinct and group
Group by is faster than distinct, regardless of whether an index is added or not.

Therefore, we recommend that you selectGroup.

The above is a comparison of the performance between distinct and group by in MySQL. do you have a better understanding of distinct and group by through the above comparison? I hope it will help you learn more.