>It is driving me crazy. I'm working on a web spider where a table >holds the downloaded >webpage. It seems that a select > >"SELECT count(*) WHERE NOT text IS NULL" > >requires that the complete text column is loaded. With a stored LOB >this results in crazy performance. > >Is this optimized in later versions of SQLite (i'm using a 3.4.x which is >about 2 years old).
select (select count(*) from mybigtable) - (select count(*) from mybigtable where mytextcolumn is null) as "Non-NULL record count"; This uses only indexes, but explain query plan gives no answer on the first select. Anyway, workaround like this to check that the second part actually uses your index (Adapt to minimum rowid you're using if ever you force negative rowids in). explain query plan select (select count(*) from mybigtable where rowid > 0) - (select count(*) from mybigtable where mytextcolumn is null) as "Non-NULL record count"; TABLE mybigtable USING PRIMARY KEY TABLE mybigtable USING idxMyTextColumn BTW, what is "irreal" in SQLite? Its cost or its support? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
