MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizer ignores datetime index
Posted by: Toa Sty
Date: October 27, 2006 12:48PM

Hi Angus,

I think you have the right reason for why it's not using the index: It thinks it would end up doing more work doing index lookups for 50% of the table than it would scanning 100% of it.

I'm pretty sure that:
SELECT * FROM t1 WHERE Start<NOW()
would use your index if it were going to retrieve a smaller set/percentage of rows, because as you say NOW() returns a constant value. (note that it wouldn't get put in the query cache though)

Have you benchmarked the queries to see which is actually quicker? The table scan may well be. In particular I'd expect it to be quicker if you have to hit physical disk for your data. Random disk access is slow...

You could look at trying different values for max_seeks_per_key I suppose, though I'm not sure you'll need to.

Toasty

-----------------------------------------
email: 'toasty'*3 at gmail

Options: ReplyQuote


Subject
Views
Written By
Posted
5335
October 27, 2006 09:51AM
Re: Optimizer ignores datetime index
3476
October 27, 2006 12:48PM
2850
October 27, 2006 01:09PM


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.