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

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

Assume the names table contains all 40 million first names of everyone living i开发者_开发知识库n 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

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

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.