At what selectivity are secondary indexes useful in SingleStore?
Secondary indexes are effective only when the required search is for at most 10% of the table rows.
If the requirement is to fetch around ~7% rows of the table data, it is not beneficial to scan the entire table. Instead, adding a secondary index to that table on the required columns helps improve the performance of the queries.
If the requirement is to fetch more than ~10% of the rows, then it is better to do a full table scan to avoid the overhead of picking and choosing a more significant percentage of individual/range of rows from the table.
Consider the STUDENTS table containing the details of all the students at a college.
Each student has a unique student_ID number along with their first and last names captured in the table. Here, the 'student_ID' number would be considered the primary key/clustered key since that uniquely identifies the student; however, if 'last_name' is used in most queries, then it is beneficial to construct a secondary index on last_name.