Hello,
my question must be rather common though I've not seen it discussed it
anywhere yet: what is the most efficient way to get all different values
of a given column with low cardinality ? For instance I have a table
with columns DAY, NAME, ID, etc. Now I would like to list all values
for DAY, only without scanning all the table each time if possible
(assume for ex that there are 10 different values for DAY among 10^6
rows in the table).
I can think of:
Solution 1: SELECT DAY FROM TABLE GROUP BY DAY;
Solution 2: SELECT DISTINCT DAY FROM TABLE;
Solution 3: Improve performance through an index scan by using DAY as
the first element of the PK, (PRIMARY KEY (DAY, ID) ), although DAY has
a low cardinality ?
Solution 4: Create a separate index on column DAY ?
Solution 5: Use some kind of view / stored procedure that would be
precomputed when TABLE is updated or cached when called for the first
time ? Does something like that exist ?
Solution 6: Store the values in a separate table, recreated each time
TABLE is updated.
This looks to me as a very common problem. Is there an obvious / best /
standard solution there ? What would be the expected performance of the
different solutions above ? (I guess some are probably non-sense)
Thank you all !
Christian