how do I optimize this sql:select count(*) from `order` where `time`>1307894400000

I need to get orders count for a day(field "time" is java timestamp,and table "order" has 1,000,000 records), I using:

se开发者_开发百科lect count(*) from `order` where `time`>1307894400000 && `time`<1307980799999

this sql used 540ms

I tried create index use field "time" ,but still need to 390ms

how do I optimize this sql statement?

If it is still not enough then your only choice is partitioning the table. Many db engines allow specifying automatic partitions.

Try with below.

select count(YourPrimaryKey) from order where time>1307894400000 && time<1307980799999