[sqlite] Symmetric EXCEPT
Is there any optimisation done for the case of a symmetric EXCEPT, to avoid performing duplicate SELECT statements? E.g.: (SELECT A... EXCEPT SELECT B...) UNION (SELECT B... EXCEPT SELECT A...) would seem to be a poor approach to getting the set of rows in either A or B. Are there potentially better generic approaches, or other tricks I've missed? (At the moment I have a query that does not use EXCEPT but is instead tied to the particulars of my schema/data, which is fine. I'm just curious about the more general case.) Jamie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with slow queries
On Tue, 2013-02-05 at 22:59 +, Simon Slavin wrote: > This might be faster if you have an index for TextNGram(text, size, > ngram). Thanks, I'll definitely try that! > > (SELECT tn.ngram > >FROM TextNGram tn, Text t > >WHERE t.label != Text.label > > AND t.label != '' > > AND tn.text = t.id > > AND tn.ngram = TextNGram.ngram > Is the last line not a test to see that "Text.label != Text.label" and > "TextNGram.ngram = TextNGram.ngram" ? I'm not sure what you mean here. The subselect should find any TextNGram.ngram that does not share the label of a matching row in the parent select, but which does share the actual ngram. Are you suggesting that the test is redundant, or incorrect, or something else? From my tests it appears to be correct, at least. > The whole thing might work faster if you first load a representative > dataset into all tables, then run the ANALYZE command. As part of the database creation step of the program that uses the database, I run ANALYZE after adding the data and indices. Jamie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I/O error on creating index with 3.7.14
On Sat, 2012-10-27 at 02:26 +0700, Dan Kennedy wrote: > Thanks for reporting this. Now fixed here: > >http://www.sqlite.org/src/info/e24ba5bee4 Excellent - thank you for the speedy fix! Jamie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I/O error on creating index with 3.7.14
Using 3.7.14, when creating an index on a 27G database (on the table that contains almost all of the data), I consistently (on Windows XP and Debian GNU/Linux, on three different machines) get a disk I/O error. This does not happen using 3.7.13 (only tested on Debian GNU/Linux), nor does it happen when creating the same index on a smaller (~2G) version of the database. I ran the process under strace; the final relevant lines (as far as I can judge, knowing nothing of this) are: lseek(5, 23934032896, SEEK_SET) = 23934032896 write(5, "\231\216\344\271\213\351\235\240\345\261\261\n+\367K\340*\5\2I \1\4\v\3 01\351\276\215\345\202\276\346\271"..., 1024) = 1024 lseek(5, 23934033920, SEEK_SET) = 23934033920 write(5, "\207\345\210\245\347\267\207\347\264\240\346\211\200\350\254 \202\346\2 11\223\351\274\223\345\274\204\n+\367u\334*\5"..., 632) = 632 lseek(5, 0, SEEK_SET) = 0 read(5, 0x7f5462cb06b8, 18446744071873782392) = -1 EFAULT (Bad address) close(5)= 0 The command that causes the error is "CREATE INDEX IF NOT EXISTS TextNGramIndex ON TextNGram (text, ngram, size)". The database schema is (without the failing index): CREATE TABLE Text ( id INTEGER PRIMARY KEY ASC, filename TEXT UNIQUE NOT NULL, checksum TEXT NOT NULL, label TEXT NOT NULL ); CREATE TABLE TextHasNGram ( text INTEGER NOT NULL REFERENCES Text (id), size INTEGER NOT NULL ); CREATE TABLE TextNGram ( text INTEGER NOT NULL REFERENCES Text (id), ngram TEXT NOT NULL, size INTEGER NOT NULL, count INTEGER NOT NULL ); CREATE UNIQUE INDEX TextHasNGramIndex ON TextHasNGram (text, size); CREATE INDEX TextIndexLabel ON Text (label); Is there more information I should provide as part of a proper bug report, or is this a known issue, or have I missed a trick somewhere? Jamie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
