Re: Bad Query Plan with Range Query

Whoops,

I meant this query (ordering my messageid):

SELECT messageID FROM Message WHERE modificationDate>= 1302627793988 ORDER BY messageID LIMIT 1;

Sometimes this gives the better plan. But not always.

On 04/15/2011 11:13 AM, Kevin Grittner wrote:
> Mark Williams<mark(dot)williams(at)jivesoftware(dot)com> wrote:
>
>> If I re-write the query like this:
>>
>> explain SELECT messageID FROM Message WHERE modificationDate>=
>> 1302627793988 ORDER BY modificationDate LIMIT 1;
>
>> I also get a better plan.
>
> Yeah, but it's not necessarily the same value. Do you want the
> minimum messageID where modificationDate>= 1302627793988 or do you
> want the messageID of some row (possibly of many) with the minimum
> modificationDate where modificationDate>= 1302627793988?
>
> Since you're asking for a logically different value with that query,
> it's not surprising it uses a different plan.
>
> -Kevin