75% of developers will fail this SQL question. What's wrong with this script? The first time I faced this question, I jumped into the SQL syntax like crazy. But the syntax was OK; the problem was in the data and related to a concept called Cardinality. Cardinality refers to the number of unique values in a column relative to a table's total number of rows. • High Cardinality means the column has many unique values. • Low Cardinality means the column has few unique values. Creating an index on a column with low Cardinality is most of the time ineffective because: 1. Low Cardinality means each indexed value points to many rows, reducing the index's ability to narrow down the search. 2. Maintaining an index has a cost of storage and update time. For low cardinality columns, this overhead might outweigh the benefits. 3. Database query optimizers are smart; they know column statistics, including Cardinality. When they detect a low cardinality index, they often ignore it and perform a full table scan instead. A simple Example Consider a table "Employees" with 1 million records. Let's examine indexing on different columns: • ID: High Cardinality (1 million unique values). An index here would be very effective. • Name: High Cardinality (many unique names). An index could be helpful in searches. • Department: Medium Cardinality (10-20 unique values). An index might sometimes be useful but less effective than an ID or Name. • Gender: Very low cardinality (2-3 unique values). The query optimizer would likely ignore an index here. When to consider a Low Cardinality column? There are scenarios where indexing a low cardinality column might be beneficial. For example, combining low and high cardinality columns can be effective. 'CREATE INDEX idx_dept_emp ON Employees(Department, ID);' Now you know the basics about Cardinality! Save this post; it might help in your next interview.