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

Question

I need to get the number of orders per day (the “time” field is the java timestamp, and the “order” table has 1,000,000 entries ), I use:

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

this sql used 540 ms

I tried to create a time index using field, but still needed 390ms

How can I optimize this sql statement?

+3

Koerr Jun 13 '11 at 5:20

2 answers

Try below.

count (YourPrimaryKey) order time > 1307894400000 && & && & time < 1307980799999

0

Pankaj 13 . '11 5:28

CAFxX · Accepted Answer · 2011-06-13T05:48:08+0000

If this is not enough, then your only choice is to split the table. Many db mechanisms allow you to specify automatic partitions.