Re: [sqlite] Partial index to find maximum
Hick Gunter wrote: >create the primary key index ordered properly > >CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); DESC is not necessary here; SQLite has no problem reading the index in reverse order, if needed. (DESC in an index is useful only when you want to optimize multiple ORDER BYs in a single query.) >SELECT b FROM t WHERE a = ? LIMIT 1; This is wrong with or without a DESC index: there is no guarantee that the returned row is the first in the index unless you're using ORDER BY or MAX(). Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Partial index to find maximum
create the primary key index ordered properly CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); SELECT b FROM t WHERE a = ? LIMIT 1; If you insist on using a partial index for this (for example if each a has a lot of b entries) you could add a field b_is_max and keep it current using triggers. CREATE INDEX t_max ON t (a,b) WHERE b_is_max; SELECT b FROM t WHERE a=? AND b_is_max; CREATE TRIGGER t_ins AFTER INSERT ON t WHEN new.b > (SELECT b FROM t WHERE a = new.a AND b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = new.a AND b_is_max; UPDATE t SET b_is_max = 1 WHERE a = new.a AND b = new.b; END; CREATE TRIGGER t_del BEFORE DELETE ON t WHEN old. b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = old.a AND b = old.b); UPDATE t SET b_is_max = 1 WHERE a = old.a AND b = (SELECT max(b) FROM t WHERE a = old.a and b < old.b); END; CREATE TRIGGER t_upd_new AFTER UPDATE ON t WHEN new.b > (SELECT b FROM t WHERE a = new.a AND b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = new.a AND b_is_max; UPDATE t SET b_is_max = 1 WHERE a = new.a AND b = new.b; END; CREATE TRIGGER t_upd_old BEFORE UPDATE ON t WHEN old.b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = old.a AND b = old.b); UPDATE t SET b_is_max = 1 WHERE a = old.a AND b =(SELECT max(b) FROM t WHERE a = old.a and b < old.b); END; Triggers shown are untested. -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Montag, 29. Dezember 2014 09:34 An: General Discussion of SQLite Database Betreff: [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
Re: [sqlite] Partial index to find maximum
-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
Re: [sqlite] Partial index to find maximum
On 29/12/2014 4:33 PM, Baruch Burstein wrote: 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? I don't know if you can do a partial index for this, but if this is a use case that is slow for you and is very critical, I would use a trigger to update a separate table that stores just the value of "a" and its corresponding maximum. It's quite easy to manage this for the create and insert cases. You may have to run that whole query in the trigger again if there a deletion done. So, in a way this table is the cache you want. Not sure if that helps your case. Best Regards, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
