All Forums Database
dnsmkl 2 posts Joined 05/11
26 Jun 2016
Any benefit in NUSI on "customer_type" column?

Hi,
Is there any point/benefit in creating secondary index on column,
which has low number of distinct values compared to size of the table.
Hypothetical example:

CREATE TABLE CUSTOMER
(
    customer_id bigint,
    customer_name varchar(10),
    customer_type smallint
)
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?

AtardecerR0j0 71 posts Joined 09/12
27 Jun 2016

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.

Be More!!

You must sign in to leave a comment.