You should include both a and b in the index to be most helpful. CREATE INDEX whatever ON t (a, b);
However, you say that (a, b) is already the primary key and therefore this index already exists and you do not need to create another one. Although the index will contain all rows, finding the max(b) given an "a" value should take just 1 index lookup. If the optimizer does not do this for you (I believe it does) then you can just do: select b from t where a=:whatever order by b desc limit 1; to find the answer you seek with nothing more than a single lookup in the index. So lets test what SQLite is going to do: On SQLite 3.8.8 (head of trunk) we see that: SQLite version 3.8.8 2014-12-25 12:19:56 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(a INTEGER, b INTEGER, primary key (a, b)); sqlite> insert into t values (1,1),(1,5),(2,3),(1,8),(2,7); sqlite> .explain sqlite> explain select max(b) from t where a=1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 21 0 00 Start at 21 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenRead 1 3 0 k(3,nil,nil,nil) 00 root=3 iDb=0; sqlite_autoindex_t_1 3 Explain 0 0 0 SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (a=?) 00 4 Noop 0 0 0 00 Begin WHERE-loop0: t 5 Integer 1 3 0 00 r[3]=1 6 SeekLE 1 15 3 1 00 key=r[3] 7 IdxLT 1 15 3 1 00 key=r[3] 8 Noop 0 0 0 00 Begin WHERE-core 9 Column 1 1 4 00 r[4]=t.b 10 CollSeq 0 0 0 (BINARY) 00 11 AggStep 0 4 1 max(1) 01 accum=r[1] step(r[4]) 12 Goto 0 16 0 00 max() by index 13 Noop 0 0 0 00 End WHERE-core 14 Prev 1 7 0 00 15 Noop 0 0 0 00 End WHERE-loop0: t 16 Close 1 0 0 00 17 AggFinal 1 1 0 max(1) 00 accum=r[1] N=1 18 Copy 1 5 0 00 r[5]=r[1] 19 ResultRow 5 1 0 00 output=r[5] 20 Halt 0 0 0 00 21 Transaction 0 0 1 0 01 22 TableLock 0 2 0 t 00 iDb=0 root=2 write=0 23 Goto 0 1 0 00 sqlite> explain select b from t where a=1 order by b desc limit 1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 00 Start at 18 1 Noop 0 0 0 00 2 Integer 1 1 0 00 r[1]=1; LIMIT counter 3 OpenRead 2 3 0 k(3,nil,nil,nil) 00 root=3 iDb=0; sqlite_autoindex_t_1 4 Explain 0 0 0 SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (a=?) 00 5 Noop 0 0 0 00 Begin WHERE-loop0: t 6 Integer 1 2 0 00 r[2]=1 7 SeekLE 2 15 2 1 00 key=r[2] 8 IdxLT 2 15 2 1 00 key=r[2] 9 Noop 0 0 0 00 Begin WHERE-core 10 Column 2 1 3 00 r[3]=t.b 11 ResultRow 3 1 0 00 output=r[3] 12 IfZero 1 16 -1 00 r[1]+=-1, if r[1]==0 goto 16 13 Noop 0 0 0 00 End WHERE-core 14 Prev 2 8 0 00 15 Noop 0 0 0 00 End WHERE-loop0: t 16 Close 2 0 0 00 17 Halt 0 0 0 00 18 Transaction 0 0 1 0 01 19 TableLock 0 2 0 t 00 iDb=0 root=2 write=0 20 Goto 0 1 0 00 sqlite> So both forms of the query will run mostly the same plan and retrieve the result you desire in a single index lookup. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Baruch Burstein >Sent: Monday, 29 December, 2014 01:34 >To: General Discussion of SQLite Database >Subject: [sqlite] Partial index to find maximum > >Hi, > >I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a >given value of 'a', the highest matching 'b'. The query itself it simple: > > SELECT max(b) FROM t WHERE a=:whatever > >To speed this up, I would add an index on 'a'. Now, the question is is >there some way to tell the index that I am only interested in the maximum >value of b? For example, for the following table: > >a|b >1|1 >1|2 >2|2 >2|3 > >I only need the index to contain the rows (1,2) and (2,3). The docs for >partial indexes say that they can't contain functions (like max()). Any >suggestions? > >-- >˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
