Re: Index not used for JOIN on compound key plus WHERE range filter

[Chad B]

Additional information, benchmarking results:

I ran the query as shown (basically, but the real table names are different), where I join "place" and "appt" and search a range on "when". On my production database, this takes on average 20 seconds to execute and returns 7171 rows.

On the other hand I can also do the join in program code. I first select the right rows from "place", then in the application code query "appt" for each place row one at a time. This still returns 7171 rows but takes 0.23 seconds!

So both approaches give 7171 rows, but letting MySQL do the join takes 20 seconds and doing it in application code takes 0.23 seconds. That's almost 2 orders of magnitude difference.

Note: In both cases I ran multiple trials, on the same machine as the database, and using SQL_NO_CACHE.

I think the optimizer is letting me down. (?)