Re: Adding limit clause takes query from 2 seconds to 42 minutes

[Josh H]

Thanks for the questions and sorry about the delay responding :-)

This is mysql version 5.5.27

The problem appears to be an optimizer bug, and I have opened a bug for it. The order of the joins is reversed if I add a limit clause.

The explain output shows the problem; the join is starting with the larger table (player_record) and then joining in the smaller table (date_curr) without using a key. The correct join, which happens if no limit is applied, is to start with date_curr and then join to player_record using the PRIMARY key.