I had a table called Attendance which have three columns Student_id, date, status. I gave the composite primary key as Student_Id, date. so index will be created for them automatically. the table has 100k rows. Student_Id has 1 to 40. Each has count of 2500. If try to query with respect to Student_Id I get different row count in explain. How is it possible?
explain select * from Attendance where Student_Id=40;
this is the query. For 40 it gives row as 50 834 and for id 2 it gives 47 638.
I got different count. But expecting same number of row.
The selectivity of an index refers to the uniqueness of values in the indexed column. If the Student_Id column has low selectivity (i.e., many rows have the same Student_Id value), MySQL may choose not to use the index for certain queries, especially if the query optimizer determines that a full table scan would be more efficient.
wheather this will be a reason.