CODEWITHSUNDEEP
sqlperformancesqlite
Sven van den Boogaart
Sven van den Boogaart

Reputation: 12323

sqlite improve query speed

I have an sqlite query that is dynamically generated like:

SELECT * FROM gsmr_data WHERE test_id='86' AND device='devicename' AND id<4371808 AND (metric='C2' OR metric='C2_N1' OR metric='C2_N2' OR metric='C2_N3' OR metric='C2_N4' OR metric='C2_N5' OR metric='C2_N6') ORDER BY id DESC LIMIT 500

Where id is based on the last result and the metric (in the OR part) are added dynamically.

My table looks like:

enter image description here

And I have the following indexes in the table:

enter image description here

The query takes long (7 seconds in sqlite db browser)

Is there anything else that I can do to improve the speed of the query?

Upvotes: 0

Views: 54

Answers (2)

Shawn
Shawn

Reputation: 52334

You're testing a bunch of columns, but your indexes are for only one column apiece, so only one of them will be used (See EXPLAIN QUERY PLAN output to see which one; running ANALYZE might change which one the query planner uses).

Using a multi-column index could allow for better performance by limiting the overall number of rows looked at (But of course, like all indexes, comes at a cost of increased time inserting, deleting, and updating rows, and taking up more space).

I'd look first at adding an index on all the columns used in the WHERE:

CREATE INDEX idx_gsmr_data ON gsmr_data(test_id, device, metric, id)

See the query optimizer documentation for why id is the last one in the index. Also see the sqlite3 shell's .expert command for suggesting indexes for queries, and an overview of how sqlite uses indexes.

Upvotes: 2

Feacio
Feacio

Reputation: 80

You could try

SELECT * FROM gsmr_data
WHERE test_id='86' AND device='devicename' AND id<4371808 AND
   metric in('C2', 'C2_N1', 'C2_N2', 'C2_N3', 'C2_N4', 'C2_N5', 'C2_N6')
ORDER BY id DESC LIMIT 500

Upvotes: 1

Related Questions