Re: Optimizer ignores datetime index

[Toa Sty]

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