Optimizer ignores datetime index
Posted by:
Angus
Date: October 27, 2006 09:51AM
I have this very simple query which I think should use a certain index on a DATETIME column, but does not:
explain SELECT * FROM t1 WHERE Start<NOW()
The column Start has its own index. The explain says that the "type" is "ALL", and correctly identifies the relevant key under "possible_keys", but then doesn't use it.
According to what I've read, the optimizer dispenses with indexes if it thinks it will read a large percentage of the table. The SELECT should return about half the rows. I've also heard that constants need to be used for an index to work. The manual says that NOW() does return a constant, but if I change it for Start<'2006-10-27' this doesn't cause the index to be used either.
USE INDEX doesn't cause the index to be used, only FORCE INDEX does. This suggests to me that the optimizer knows something that I don't, and FORCE INDEX is suboptimal.
The database is InnoDB and the server is 5.1
Subject
Views
Written By
Posted
Optimizer ignores datetime index
5322
October 27, 2006 09:51AM
3470
October 27, 2006 12:48PM
2841
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.