Thanks for the tip. This field is actually boolean, but also nullable. From other languages (like Python and JS) I actually assumed the NULL check is faster than value comparison. Will improve later (for now it's definitely not a bottleneck).
On Tue, Oct 31, 2017 at 3:50 PM Simon Slavin <slav...@bigfraud.org> wrote: > > > On 31 Oct 2017, at 10:21am, Eugene Mirotin <emiro...@gmail.com> wrote: > > > Hmm, I think I've found the solution: > > > > INSERT INTO search (rowid, question, answer, altAnswers, comments, > authors, > > sources) SELECT id, question, answer, altAnswers, comments, authors, > > sources FROM questions WHERE obsolete IS NULL; > > That looks like it should work, and the "INSERT … SELECT" form is very > fast. > > I do have a suggestion for something else: don’t use intentionally NULL > values. NULL means "unknown" or "no value" in SQL whereas what you mean is > more like TRUE and FALSE. > > SQLite doesn’t have a BOOLEAN type. Instead the fastest, most compact way > to store booleans is to use 0 and 1. And if you store those values you can > do things like > > … WHERE obsolete — equivalent to WHERE obsolete = 1 > … WHERE NOT obsolete — equivalent to WHERE obsolete = 0 > > Unfortunately TRUE and FALSE are not reserved words, so you do have to do > things like > > CREATE TABLE questions (…, obsolete INTEGER DEFAULT 0, …). > > Using specific values 0 and 1 means that if you have a fault in your > software and end up with NULL values in your fields you know something > definitely went wrong. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
