CODEWITHSUNDEEP
sqlperformancesqlite
Daniel
Daniel

Reputation: 2592

SQLite slow select query - howto make it faster

In SQLite I have a large DB (~35Mb).

It contains a table with the following syntax:

CREATE TABLE "log_temperature" (
"id"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"date"  datetime NOT NULL,
"temperature"  varchar(20) NOT NULL
)

Now when I want to search for datas within a period, it is too slow on an embedded system:

$ time sqlite3 sample.sqlite "select MIN(id) from log_temperature where 
date BETWEEN '2019-08-13 00:00:00' AND '2019-08-13 23:59:59';"
331106

real    0m2.123s
user    0m0.847s
sys     0m0.279s

Note1: ids are running from 210610 to 331600.

Note2: if I run 'SELECT id FROM log_temperature ORDER BY ASC LIMIT 1', it gives the exact same timing as with the 'MIN' function.

I want to have the 'real time of 0m2.123s' to be as close to 0m0.0s as possible.

What are my options for making this faster? (Without removing hundreds of thousands of data?)

ps.: embedded system parameters are not important here. This shall be solved by optimizing the query or the underlying schema.

Upvotes: 2

Views: 1087

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

First, I would recommend that you write the query as:

select MIN(id)
from log_temperature 
where date >= '2019-08-13' and date < '2019-08-14';

This doesn't impact performance, but it makes the query easier to write -- and no need to fiddle with times.

Then, you want an index on (date, id):

create index idx_log_temperature_date_id on log_temperature(date, id);

I don't think id is needed in the index, if it is declared as the primary key of the table.

Upvotes: 5

Patrick
Patrick

Reputation: 5846

Can you create an index on the date?

CREATE INDEX index_name ON log_temperature(date);

Upvotes: 2

Related Questions