For a member table with 1 million rows, consider creating a B+ tree index on each of the columns member_no (all different), prefecture (47 values) and gender (2 values, split roughly in half). When searching by specifying a value for one column in the WHERE clause, on which column is an index likely to be least effective?

Answer: B. gender

An index is effective when the rows that meet the condition are narrowed down to a small fraction of the whole. Every member_no is different, so only one row matches and the index leads straight to it. Prefecture has 47 values, so if members are evenly spread, one value narrows the search to about 1 million ÷ 47 ≈ 21000 rows (about 2% of the total). Gender has two values split roughly in half, so either value matches about 500000 rows (about 50%). Fetching that many rows one at a time through an index is often slower than a full table scan reading the table from start to finish, so the DBMS optimizer may choose an execution plan that does not use the index. In this way, the more distinct values a column has and the more strongly a condition on it narrows down the rows (the more selective it is), the more effective an index on it is.

• A. Prefecture has 47 values, and one value narrows the rows to about 2% of the total, so an index is more effective than for gender.
• B. Correct. Either value matches about half the rows, so an index gives almost no benefit.
• C. Every member_no is different and narrows the search to one row, so this is the column where an index is most effective.