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
?
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!