MySQL: Index for fast DISTINCT queries?
mysql, query-optimization
Solution
In this case, an index on `groupname` should get you the best possible results.
If that's not good enough, a couple more options to consider - first, you could cache the results of that query so that you only run it when you absolutely have to. Second, you could create a separate table to store the `groupname` values and populate it via an insert trigger (this would avoid having to change your CSV import process)
Problem
What index(es) to I need to set to get results as fast as possible for DISTINCT queries on a certain column? Example table columns: ``` id INTEGER name VARCHAR(32) groupname VARCHAR(16) ``` Every so often I need to get a list of all groups, ``` SELECT DISTINCT groupname FROM data ORDER BY groupname ``` The table can have > 200k entries, but only about a dozen groups. I would like to not use a separate table for the group names, because the data get imported often from a CSV file.