Counting Distinct Values in large dataset (40M rows): SELECT count(*) as count, name FROM names GROUP BY name ORDER BY name;

Question

CREATE TABLE `names` ( `name` varchar(20) );

Assume the "names" table contains all 40 million first names of everyone living in California (for example).

SELECT count(*) as count, name FROM names GROUP BY name ORDER BY name;

How can I optimize this query?

Expected Result:

count | name
9999 | joe
9995 | mike
9990 | kate
...
2 | kal-el

Answer 1

Well, what makes you think it's not already optimised? This looks like the sort of query a good database engine should be able to handle relatively easily - particularly if you've got an appropriate index on your table.

Do you actually have a bottleneck here, or are you worrying about something that might happen in the future? If it's the latter, I suggest you try it with your RDBMS (by generating dummy data), and see what happens.

Answer 2

You have to create an index on the name column of your table. The query is as good as it can be.