MySQL Forums
Forum List  »  Optimizer & Parser

Re: Index not used for JOIN on compound key plus WHERE range filter
Posted by: Chad B
Date: August 15, 2013 10:44AM

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. (?)

Options: ReplyQuote

Written By
Re: Index not used for JOIN on compound key plus WHERE range filter
August 15, 2013 10:44AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.