Comparison of performance between distinct and group by in Mysql _mysql

Recently saw a number of online tests, the feeling is not very accurate, today personally tested some. It is concluded that the testing process on personal computers may not be comprehensive enough for reference only.

Test process:
Prepare a test sheet

CREATE TABLE ' test_test ' (
  ' id ' int () NOT NULL auto_increment,
  ' num ' int (one) not null default ' 0 ',
  PRIMARY K EY (' id ')
  ) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;

Create a stored procedure to insert 10W data into a table

 CREATE PROCEDURE p_test (PA int (one)) 
  begin 
  
  Declare max_num int (one) default 100000; 
  declare i int default 0; 
  declare rand_num int; 
  
  Select COUNT (ID) into the max_num from Test_test; 
  
  While I < PA does 
    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 stored Procedure Insert data

Call P_test (100000);

Start test: (without index)

 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.078ms
  
 [SQL] 
 Select num from test_test GROUP by NUM;
 Affected rows: 0
 time: 0.031ms

Two, the NUM field to create the index

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.000ms
  
 [SQL] 
 Select num from test_test GROUP by NUM;
 Affected rows: 0
 time: 0.000ms

At this point we found that the time is too small for 0.000 seconds can not be accurate.
We 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 added The distinct is 107 times times faster than the distinct without index.
group by is 43 times times faster than group by without index after indexing.
Compare:distinct and GROUP by
No index group BY is faster than distinct.

therefore, when using the recommended GROUP BY.

The above is in MySQL distinct and group by the performance comparison, through the above comparison is not to distinct and group by have a more in-depth understanding, hope for everyone's learning help.