1

This query is taking too long (over 15000 records on a 800mhz cpu):

select count(*) from events where millis  >  date("now", "start of month")

The field is defined as millis INT, and when I run the query (or any other variation I have come up with) sqlite seems to do a date conversion on every record it compares. I saw that with strace:

read(3, "\r\0\0\0\21\0H\0\3\310\3\220\3X\3 \2\350\2\260\2x\2@\2\10\1\320\1\230\1`"..., 1024) = 1024
gettimeofday({1399595165, 605812}, NULL) = 0
gettimeofday({1399595165, 609833}, NULL) = 0
gettimeofday({1399595165, 611851}, NULL) = 0
gettimeofday({1399595165, 618546}, NULL) = 0
gettimeofday({1399595165, 623135}, NULL) = 0
gettimeofday({1399595165, 626291}, NULL) = 0
gettimeofday({1399595165, 634558}, NULL) = 0
gettimeofday({1399595165, 636463}, NULL) = 0
gettimeofday({1399595165, 638986}, NULL) = 0
gettimeofday({1399595165, 669426}, NULL) = 0
gettimeofday({1399595165, 675431}, NULL) = 0
...
And it keeps running

¿Is there an efficient way to get a count of the records belonging to the current day, converting "now" to an int only once and doing simple math after that?

Edit: As CL pointed out, just by precomputing the date was enough. No need to cast it to an int. The following works faster (no date comparisons):

select date( "now", "-1 day");
2014-05-08
sqlite> select count(millis) from events where millis > "2014-05-08";
1177

The other thing that drastically improved performance was creating an index on the column, just as one would do on any other DB engine. Don't know why I supposed SQLite didn't support them.

Just for the record:

sqlite> create index ievemts_millis on events (millis);
sqlite> select date( "now", "-1 day");
2014-05-08
sqlite> select count(millis) from events where millis > "2014-05-08";
1177
marianov
  • 875
  • 1
  • 9
  • 19

1 Answers1

1

For a simple value like this, you could use two queries:

SELECT date('now', 'start of month');

SELECT COUNT(*) FROM events WHERE millis > ?;

(It might be easier to just compute the milliseconds value in your application's code.)

Recent enough SQLite versions will evaluate the date function only once.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • `SELECT date('now', 'start of month');` results in: `2014-05-01` – marianov May 09 '14 at 13:09
  • My mistake. `select count(*) from bicis where millis > "2014-05-08" ` works as expected, even though the date is written as a string ant not an INT as I expected. SQLite does a full scan, but no date operations: `_llseek(3, 6774784, [6774784], SEEK_SET) = 0 read(3, "\r\0\0\0\20\0`\0\3\306\3\214\3R\3\30\2\336\2\244\2j\0020\1\366\1\274\1\202\1H"..., 1024) = 1024 ` – marianov May 09 '14 at 13:18