What's faster, SELECT DISTINCT or GROUP BY in MySQL?

If I have a table

CREATE TABLE users (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  profession varchar(255) NOT NULL,
  employer varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)

and I want to get all unique values of profession field, what would be faster (or recommended):

SELECT DISTINCT u.profession FROM users u

or

SELECT u.profession FROM users u GROUP BY u.profession

?

 Answers

They are essentially equivalent to each other (in fact this is how some databases implement DISTINCT under the hood).

If one of them is faster, it's going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking advantage of any group members, just their keys. DISTINCT makes this explicit, so you can get away with a slightly dumber optimizer.

When in doubt, test!

---

Go for the simplest and shortest if you can -- DISTINCT seems to be more what you are looking for only because it will give you EXACTLY the answer you need and only that!

---

It seems that the queries are not exactly the same. At least for MySQL.

Compare:

1. describe select distinct productname from northwind.products
2. describe select productname from northwind.products group by productname

The second query gives additionally "Using filesort" in Extra.

---

(more of a functional note)

There are cases when you have to use GROUP BY, for example if you wanted to get the number of employees per employer:

SELECT u.employer, COUNT(u.id) AS "total employees" FROM users u GROUP BY u.employer

In such a scenario DISTINCT u.employer doesn't work right. Perhaps there is a way, but I just do not know it. (If someone knows how to make such a query with DISTINCT please add a note!)

---

After heavy testing we came to the conclusion that GROUP BY is faster

SELECT sql_no_cache opnamegroep_intern FROM telwerken WHERE opnemergroep IN (7,8,9,10,11,12,13) group by opnamegroep_intern

635 totaal 0.0944 seconds Weergave van records 0 - 29 ( 635 totaal, query duurde 0.0484 sec)

SELECT sql_no_cache distinct (opnamegroep_intern) FROM telwerken WHERE opnemergroep IN (7,8,9,10,11,12,13)

635 totaal 0.2117 seconds ( almost 100% slower ) Weergave van records 0 - 29 ( 635 totaal, query duurde 0.3468 sec)

---

Here is a simple approach which will print the 2 different elapsed time for each query.

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT DISTINCT u.profession FROM users u; --Query with DISTINCT
SET @t2 = GETDATE();
PRINT 'Elapsed time (ms): ' + CAST(DATEDIFF(millisecond, @t1, @t2) AS varchar);

SET @t1 = GETDATE();
SELECT u.profession FROM users u GROUP BY u.profession; --Query with GROUP BY
SET @t2 = GETDATE();
PRINT 'Elapsed time (ms): ' + CAST(DATEDIFF(millisecond, @t1, @t2) AS varchar);

OR try SET STATISTICS TIME (Transact-SQL)

SET STATISTICS TIME ON;
SELECT DISTINCT u.profession FROM users u; --Query with DISTINCT
SELECT u.profession FROM users u GROUP BY u.profession; --Query with GROUP BY
SET STATISTICS TIME OFF;

It simply displays the number of milliseconds required to parse, compile, and execute each statement as below:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

---

If the problem allows it, try with EXISTS, since it's optimized to end as soon as a result is found (And don't buffer any response), so, if you are just trying to normalize data for a WHERE clause like this

SELECT FROM SOMETHING S WHERE S.ID IN ( SELECT DISTINCT DCR.SOMETHING_ID FROM DIFF_CARDINALITY_RELATIONSHIP DCR ) -- to keep same cardinality

A faster response would be:

SELECT FROM SOMETHING S WHERE EXISTS ( SELECT 1 FROM DIFF_CARDINALITY_RELATIONSHIP DCR WHERE DCR.SOMETHING_ID = S.ID )

This isn't always possible but when available you will see a faster response.

Mysql: The SELECT statement does not work correctly

I'm using this code to retrieve data from a table I created.

SELECT id,shirt_name,boys FROM shirts WHERE boys IS NOT NULL

Instead of just selecting the rows where the boys column has input, it selects all of them. here's the way I created the table

CREATE TABLE shirts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    shirt_name VARCHAR(20) NOT NULL,
    men VARCHAR(10) NULL,
    women VARCHAR(10) NULL,
    boys VARCHAR(10) NULL,
    girls VARCHAR(10) NULL,
    babies VARCHAR(10) NULL,
)ENGINE=INNODB;

INSERT INTO shirts(shirt_name,men,women,boys,girls,babies) VALUES
    ('Crewneck Tee','me_crn','wo_crn','bo_crn','gi_crn','ba_crn'),
    ('V-Neck Tee','me_vnc','wo_vnc','','',''),
    ('Scoop Neck Tee','','wo_sco','','',''),
    ('Raglan Tee','me_rag','wo_rag','bo_rag','gi_rag',''),
    ('Ringer Tee','me_rin','wo_rin','bo_rin','gi_rin',''),
    ('Cap Sleeve Tee','','wo_cap','','gi_cap',''),
    ('Tank Top','me_tan','wo_tan','bo_tan','gi_tan',''),
    ('Spaghetti Strap','','wo_spa','','',''),
    ('Hoodie','me_hod','wo_hod','bo_hod','gi_hod','ba_hod');

what did I do wrong?

---

SELECT id,shirt_name,boys FROM shirts WHERE boys != ''

Is what you need to use with this data

MYSQL where statement does not work as expected

I'm trying to count the number of rows in this query, however it's not working as expected, this returns an extra row, it should be 12, but it's 13.

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id
FROM albums, albumData
WHERE
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

When I remove the OR statement part, and not count the rows with albumID=0, it returns 11. There is only one row where the albumID is 0, but it counts it as two?

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id
FROM albums, albumData
WHERE
albumData.id=albums.albumID
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

---

Try to write query using JOIN:

SELECT albums.id FROM albums
LEFT JOIN albumData
   ON albums.albumID=albumData.id
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

or

SELECT albums.id FROM albums
LEFT JOIN albumData
   ON albums.albumID=albumData.id
WHERE albums.userID=$id
  AND albums.state=0
  AND albumData.state=0

Does this solve your trouble?

EDITED:
Try this

SELECT DISTINCT albums.id FROM albums
INNER JOIN albumData
   ON (albums.albumID = albumData.id OR albums.albumID = 0)
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

Mysql select account on the left join with the condition does not work

I need to count number of records on left table, i read other questions and end up with this query but the condition on COUNT is ignored

SELECT  a.name, COUNT( f.status <> 'e' ) AS total
FROM    album AS a LEFT JOIN photo AS f
        ON a.id = f.idalbum
WHERE   a.iduser = 4
GROUP   BY a.id

is MySQL 5 DB

---

you cant specify a condition inside COUNT statment.

try this

   SELECT  a.name, COUNT( f.status  ) AS total
   FROM    album AS a LEFT JOIN photo AS f
    ON a.id = f.idalbum
   WHERE   a.iduser = 4 or f.status <> 'e'
   GROUP   BY a.id

Mysql: Select values that have all ID from array

We have a table film_actor that represents many to many relationship between films and actors.

CREATE TABLE film_actor(
  film_id INT(11) NOT NULL,
  actor_id INT(11) NOT NULL,
  PRIMARY KEY (film_id, actor_id)
);
INSERT INTO film_actor VALUES 
  (1, 5),
  (1, 6),
  (1, 8),
  (1, 10),
  (2, 5),
  (2, 10),
  (2, 15),
  (3, 5),
  (3, 8),
  (3, 10),
  (4, 5),
  (4, 8);

Suppose, we want to find all films where the only actors from a given array { 5, 8, 10 } were starring:

SELECT film_id FROM film_actor
GROUP BY
  film_id
HAVING 
  COUNT(IF(actor_id = 5, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 8, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 10, 1, NULL)) > 0 AND
  COUNT(IF(actor_id <> 5 AND actor_id <> 8 AND actor_id <> 10, 1, NULL)) = 0;
+---------+
| film_id |
+---------+
|       3 |
+---------+

The next query will return the same films:

SELECT film_id, GROUP_CONCAT(actor_id ORDER BY actor_id) AS actors FROM film_actor
GROUP BY
  film_id
HAVING
  actors = '5,8,10';
+---------+--------+
| film_id | actors |
+---------+--------+
|       3 | 5,8,10 |
+---------+--------+

Mysql: Select N latest records in a group

Example demonstrates a way to find n-maximum records in the group.

CREATE TABLE comments(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  post_id INT(11)
);
INSERT INTO comments VALUES 
  (1, 1),
  (2, 1),
  (3, 2),
  (4, 3),
  (5, 1),
  (6, 1),
  (7, 3),
  (8, 1);

Select 2 latest comments by post:

SELECT id, post_id FROM 
  (
  SELECT c1.*, COUNT(*) c_num FROM comments c1
    LEFT JOIN comments c2
      ON c2.post_id = c1.post_id AND c2.id <= c1.id
  GROUP BY
    c1.post_id, c1.id
  ) t
WHERE
  c_num <= 2;
+----+---------+
| id | post_id |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       2 |
|  4 |       3 |
|  7 |       3 |
+----+---------+

Mysql: Select latest one from each category (group-wise max in a group query)

Suppose we have this 'catalogs' table 
CREATE TABLE catalogs(
  id INT(11) NOT NULL,
  cat_id INT(11) DEFAULT NULL,
  name VARCHAR(50) DEFAULT NULL,
  `date` DATE DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO catalogs VALUES 
  (1, 1, 'suzy', '2011-09-15'),
  (2, 2, 'andy', '2011-10-01'),
  (3, 1, 'dony', '2010-12-25'),
  (4, 3, 'harry', '2010-01-05'),
  (5, 2, 'matty', '2011-06-01'),
  (6, 3, 'samy', '2010-11-02'),
  (7, 1, 'honey', '2011-10-03');

This query will select the latest one from each category -

SELECT t1.cat_id, t1.id FROM catalogs t1
  JOIN (
        SELECT cat_id, MAX(date) last_date FROM catalogs
          GROUP BY cat_id
        ) t2
    ON t1.cat_id = t2.cat_id AND t1.date = t2.last_date
ORDER BY t1.cat_id;
+--------+----+
| cat_id | id |
+--------+----+
|      1 |  7 |
|      2 |  2 |
|      3 |  6 |
+--------+----+

Other way with single query
SELECT 
cat_id
,SUBSTRING_INDEX(GROUP_COCNAT(id ORDER BY date DESC),',',1) AS max_date_id
FROM 
catalogs
GROUP BY
cat_id;

Mysql: How in mysql do I select a specific column followed by all columns

SELECT column2 new_name, t.*  FROM table_name t

MYSQL - SELECT from multiple rows, same user, different values

Below is my table called fittest. I need to find which students based on student id (studid) have taken the pre and post test as designated in the prepost column. So based on the simple table below I would need to return studid 123456. How do I write the SELECT query for this?

SELECT studid, prepost FROM `fittest` LIMIT 0, 30 ; 

    studid  prepost
    123456  pre
    123456  post
    1031460 pre

Solution:

CREATE TABLE fittest (`studid` int, `prepost` varchar(4));
 
INSERT INTO fittest(`studid`, `prepost`) 

VALUES (123456, 'pre'),(123456, 'post'),(1031460, 'pre');  

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2
;

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING (MAX(prepost = 'pre' ) +
        MAX(prepost = 'post')) = 2
   AND COUNT(DISTINCT prepost) = 2;

Output
studid
123456

mysql query To get the top two salary from each department

**Department table name** 
**following with fields name**

 id , empid ,salary ,departid ,status

how to get the top two highest salaries from each department with single query in mysql

Try

SELECT id, empid, salary, departid, status
  FROM 
(
  SELECT id, empid, salary, departid, status, 
         @n := IF(@g = departid, @n + 1, 1) rownum,
         @g := departid
    FROM table1
   ORDER BY departid, salary DESC 
) q
 WHERE q.rownum <= 2

What it does it generates in inner select a rank for each employee in each department based on the salary. Then in outer select it filters out all rows that have rank more than 2 (top two).

Mysql: How to select rows with multiple specific column values in single query?

Table A

itemNo   colorNo
1        3
1        4
2        4
2        70
3        9
3        10

Try

SELECT *
  FROM A
 WHERE (itemNo = '1' AND colorNo =  '4')
    OR (itemNo = '2' AND colorNo = '70')
    OR (itemNo = '3' AND colorNo =  '9')

or you can also do this
SELECT * FROM A WHERE (itemNo, colorNo) IN ((1, 4),(2, 70),(3, 9)) Output:
| ITEMNO | COLORNO |
--------------------
| 1 | 4 |
| 2 | 70 |
| 3 | 9 |

Mysql: converting comma separated values into rows in mysql

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value

  FROM table1 t CROSS JOIN 

(

   SELECT a.N + b.N * 10 + 1 n

     FROM 

    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a

   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b

    ORDER BY n

) n

 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))

 ORDER BY value