Ok, since my question got no answer on the general list, I'm reposting
it here since this list seems in fact better suited to it.
Does anyone here know what is the most efficient way to list all
different values of a given column with low cardinality ? For instance
I have a table with columns DAY, NAME, ID, etc. The table is updated
about each week with thousands of records with the same (current) date.
Now I would like to list all values for DAY, only if possible without
scanning all the table each time I submit the request.
I can think of:
Solution 1: SELECT DAY FROM TABLE GROUP BY DAY;
Solution 2: SELECT DISTINCT DAY FROM TABLE;
(BTW why do those two yield such different performances, the later being
seemingly *much* slower than the former ?)
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