79568004

Date: 2025-04-11 04:41:15
Score: 0.5
Natty:
Report link

The difference is:

SELECT * needs all the columns, not just the customer_id.

idx_state_point is good for filtering the rows but it does not include all columns, so MySQL has to do a "bookmark lookup" (i.e. "row lookup") to fetch the rest of the row from the actual table.

MySQL might decide it's faster to use just the state index to fetch all matching rows and then do lookups or it might use a full table scan depending on the row distribution and statistics.

The optimizer thinks idx_state_point is not covering, so it switches to the simpler index or access pattern (in this case, index on state only) which is causing a larger scan (122 rows).

Reasons:
  • Long answer (-0.5):
  • No code block (0.5):
  • Low reputation (0.5):
Posted by: Deepak Saini