SQLite query performance

Question

I'm new to SQLite and I need a query as performant as possible. I have a database which stores positions of various astronomical objects and I want to access their positions through my t REAL PRIMARY KEY that is time. My query is something like

    SELECT * FROM positions ORDER BY ABS(t-t0) LIMIT 1;

where t0 is the time at which I want to see positions. This query work with primary key and I think this make it enough efficient but I don't know if the math of ORDER BY ABS(...) slow down the search, even though I suspect that this implies a full table scan.

First question: The REAL PRIMARY KEY is a problem for efficiency with respect to the ordinary INTEGER PRIMARY KEY?

Second question: If I could find the wanted PRIMARY KEY t0 from my android app code and do the query like:

    SELECT * FROM positions WHERE t = t0;

would I improve the efficiency? There is no a full table scan in this case, correct?

CL. · Accepted Answer · 2015-07-09 16:43:51Z

1

An INTEGER PRIMARY KEY is slightly more efficient than other types, but if your values do not fit into INTEGERs, you have no choice.

In general, SQLite cannot use indexes to optimize function calls.

A simple lookup would indeed be more efficient, but it is possible to rewrite the original query to use only basic comparisons when accessing the table:

SELECT *
FROM (SELECT *
      FROM positions
      WHERE t >= t0
      ORDER BY t ASC
      LIMIT 1)
UNION ALL
SELECT *
FROM (SELECT *
      FROM positions
      WHERE t < t0
      ORDER BY t DESC
      LIMIT 1)
ORDER BY ABS(t - t0)
LIMIT 1

The final ORDER BY is executed on the temporary result, which has only two rows.

answered Jul 9, 2015 at 16:43

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite query performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related