Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Rob Wultsch (wultschgmail.com)
Date: Sun Mar 16 2008 - 10:12:17 CDT
On Sun, Mar 16, 2008 at 5:48 AM, Waynn Lue <waynnluegmail.com> wrote:
> Say I have this schema
> CREATE TABLE temp (
> EntityId BIGINT AUTO_INCREMENT PRIMARY KEY,
> CreationTime DEFAULT NOW()
> Now let's say I want to find all rows created within the last 24 hours. If I do
> select * from temp where CreationTime > DATE_SUB(NOW(), INTERVAL 24 HOUR)
> that's going to do a full table scan to find out, even though there's
> already an implicit ordering in EntityId (this is of course assuming I
> don't manually set CreationTime to something else). Is there any way
> to take advantage of the fact that there's a primary key index on
> entityId, or do I have to put a secondary index on CreationTime?
First off you have not specified a data type for CreationTime . You
probably meant timestamp.
You need a second index on CreationTime.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql