Is there any point/benefit in creating secondary index on column,
which has low number of distinct values compared to size of the table.
CREATE TABLE CUSTOMER
UNIQUE PRIMARY INDEX (customer_id)
INDEX (customer_type) -- Is this index on customer_type worthless?
SELECT count(*), count(distinct customer_type) FROM CUSTOMER;
| count(*) | count(distinct customer_type) |
| 10000000 | 200 |
(also lets assume data is distributed equally between customer_types)
Am I right in thinking that such index is kind of pointless?
That NUSI doesn't look selective enough. Nevertheless you can create it, collect statistics on the index, and then do an explain referencing the NUSI. If the parser chooses a FTS over using the NUSI, drop the index.