CODEWITHSUNDEEP
performancesqlite
Toxiro
Toxiro

Reputation: 612

SQLite SELECT with max() performance

I have a table with about 1.5 million rows and three columns. Column 'timestamp' is of type REAL and indexed. I am accessing the SQLite database via PHP PDO.

The following three selects run in less than a millisecond:

select timestamp from trades
select timestamp + 1 from trades
select max(timestamp) from trades

The following select needs almost half a second:

select max(timestamp) + 1 from trades

Why is that?

EDIT: Lasse has asked for a "explain query plan", I have run this within a PHP PDO query since I have no direct SQLite3 command line tool access at the moment. I guess it does not matter, here is the result:

explain query plan select max(timestamp) + 1 from trades:
    [selectid] => 0
    [order] => 0
    [from] => 0
    [detail] => SCAN TABLE trades (~1000000 rows)

explain query plan select max(timestamp) from trades:
    [selectid] => 0
    [order] => 0
    [from] => 0
    [detail] => SEARCH TABLE trades USING COVERING INDEX tradesTimestampIdx (~1 rows)

Upvotes: 4

Views: 1939

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

The reason this query

select max(timestamp) + 1 from trades

takes so long is that the query engine must, for each record, compute the MAX value and then add one to it. Computing the MAX value involves doing a full table scan, and this must be repeated for each record because you are adding one to the value.

In the query

select timestamp + 1 from trades

you are doing a calculation for each record, but the engine only needs to scan the entire table once. And in this query

select max(timestamp) from trades

the engine does have to scan the entire table, however it also does so only once.

From the SQLite documentation:

Queries that contain a single MIN() or MAX() aggregate function whose argument is the left-most column of an index might be satisfied by doing a single index lookup rather than by scanning the entire table.

I emphasized might from the documentation, because it appears that a full table scan may be necessary for a query of the form SELECT MAX(x)+1 FROM table if column x be not the left-most column of an index.

Upvotes: 5

Related Questions