2015-12-12 21:10 GMT+01:00 Simon Slavin <slavins at bigfraud.org>:
>
> On 12 Dec 2015, at 7:52pm, Cecil Westerhof <cldwesterhof at gmail.com> wrote:
>
> > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said
> > that you should not use an index on columns that use a high number of
> NULL
> > values. At the moment that is true. So I should not use an Index??
>
> The tutorial thinks that you would use the index to pick out specific
> values in the column. Your own use is to differentiate between columns
> which have values and those which don't.
>
> Try it. Create the index. I promise it won't corrupt your database.
> Check to see whether it improves your times for the SELECT I quoted. If it
> turns out not to improve things then you can delete the index with no harm
> done.
>
?At the moment the select takes most of the time one millisecond. ?So I am
afraid I will not see an improvement. But in the future I probably also
want to select on date (what will be in used when it is not NULL), so for
then it would be handy to have an index.
Well lets just do it. ;-)
It is a bit strange: after creating the index the database is still 176 KB.
(It is a small one: I just started.) I would have expected the index to
make the file bigger.
Also now:
SELECT Total
, Used
, Total - Used as Free
FROM (
SELECT COUNT(*) AS Total
, COUNT(used) AS Used
FROM proverbs
)
is faster as:
SELECT COUNT(*) AS Total
, SUM(used IS NOT NULL) AS Used
, SUM(used IS NULL) AS Free
FROM proverbs
While before the index it was the other way around. It looks like the first
is speeded up and the second slowed down. But it is nothing to really worry
about I think.
--
Cecil Westerhof
