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?

Thanks,
Waynn

On Sun, Mar 16, 2008 at 5:48 AM, Waynn Lue <waynnlue@gmail.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?
>
> Thanks,
> Waynn

First off you have not specified a data type for CreationTime . You
probably meant timestamp.

You need a second index on CreationTime.

--
Rob Wultsch