MIN()/MAX() performance thought
(1) By anonymous on 2021-07-28 19:35:32 [source]
3.36.0, amalgamation. Given a table t with a column c with an index ON t (c), I find that SELECT MIN(c), MAX(c) FROM t; is substantially slower than SELECT MIN(c) FROM t; SELECT MAX(c) FROM t; (though the difference is obvious to a human only when t is at least moderately large). Looking at EXPLAIN QUERY PLAN output, this is understandable (scanning the table versus just looking up the extrema in the index). I'm wondering if the former form is worth special-casing; I certainly find myself doing it often enough in the shell.
(2) By anonymous on 2021-08-26 16:32:13 in reply to 1 [link] [source]
Here's a concrete example:
CREATE TABLE foo (a TEXT);
CREATE INDEX foo_a ON foo (a);
INSERT INTO foo (a) VALUES ('1234');
INSERT INTO foo (a) VALUES ('2345');
INSERT INTO foo (a) VALUES ('3456');
EXPLAIN QUERY PLAN SELECT MIN(a), MAX(a) FROM foo;
EXPLAIN QUERY PLAN SELECT MIN(a) FROM foo;
EXPLAIN QUERY PLAN SELECT MAX(a) FROM foo;
When I run this with the command-line shell on an initially nonexistent database
(as in "shell foo.db < foo.sql"), I get
QUERY PLAN
`--SCAN foo
QUERY PLAN
`--SEARCH foo USING COVERING INDEX foo_a
QUERY PLAN
`--SEARCH foo USING COVERING INDEX foo_a
(3) By J.M. Aranda (JMAranda) on 2021-08-26 18:52:33 in reply to 1 [link] [source]
Perhaps it requires more precise definitions, such as unique or primary key. All in all, evaluating aggregates with a quick read (fast-full-scan) seems acceptable to me. In current systems, with large read caches, it is fast. You also get the additional benefit of updating all the information in the table.