Hi, I notice that when I have an index on column x, "SELECT MIN(x) FROM table" and "SELECT MAX(x) FROM table" will use the index, but "SELECT MIN(x), MAX(x) FROM table" won't. See the output below.
I'm not sure how the optimizer should properly handle this case, but obviously it could be done. Would this require a cost-based optimizer (which it seems SQLite doesn't have) to do correctly? -Ken sqlite> EXPLAIN SELECT MIN(sentence) FROM tokens; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- --------------------- 0 ColumnName 0 0 MIN(sentence) 1 VerifyCookie 0 1944 2 Integer 0 0 3 OpenRead 0 4 tokens 4 Integer 0 0 5 OpenRead 1 3 (tokens autoindex 1) 6 Rewind 1 0 7 IdxRecno 1 0 8 Close 1 0 9 MoveTo 0 0 10 Column 0 0 11 Callback 1 0 12 Close 0 0 13 Halt 0 0 sqlite> EXPLAIN SELECT MAX(sentence) FROM tokens; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- --------------------- 0 ColumnName 0 0 MAX(sentence) 1 VerifyCookie 0 1944 2 Integer 0 0 3 OpenRead 0 4 tokens 4 Integer 0 0 5 OpenRead 1 3 (tokens autoindex 1) 6 Last 1 0 7 IdxRecno 1 0 8 Close 1 0 9 MoveTo 0 0 10 Column 0 0 11 Callback 1 0 12 Close 0 0 13 Halt 0 0 sqlite> EXPLAIN SELECT MIN(sentence), MAX(sentence) FROM tokens; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- ----------------------- 0 ColumnName 0 0 MIN(sentence) 1 ColumnName 1 0 MAX(sentence) 2 AggReset 0 2 3 AggInit 0 0 ptr(0x2de28) 4 AggInit 0 1 ptr(0x2de50) 5 String 0 0 6 AggFocus 0 0 7 Integer 0 0 8 OpenRead 0 4 tokens 9 VerifyCookie 0 1944 10 Rewind 0 18 11 Column 0 0 12 Integer 0 0 13 AggFunc 0 1 ptr(0x2de28) 14 Column 0 0 15 Integer 1 0 16 AggFunc 0 1 ptr(0x2de50) 17 Next 0 11 18 Close 0 0 19 AggNext 0 24 20 AggGet 0 0 21 AggGet 0 1 22 Callback 2 0 23 Goto 0 19 24 Noop 0 0 25 Halt 0 0 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
