CODEWITHSUNDEEP
androidperformancesqlite
bluePhlavio
bluePhlavio

Reputation: 547

SQLite query performance

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?

Upvotes: 1

Views: 98

Answers (1)

CL.
CL.

Reputation: 180020

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.

Upvotes: 1

Related Questions