On 31 Jul 2018, at 2:59pm, Rob Willett <rob.sql...@robertwillett.com> wrote:
> We've created a new table based on your ideas, moved the collate into the
> table, analysed the database. We did **not** add COLLATE NOCASE to the
> columns which are defined as integers. Would that make a difference?
What you did is correct. I gave wrong advice for which I apologise. But I am
now confused since your original code is a little strange. Your original has a
table definition including
"version" integer NOT NULL,
but then
CREATE UNIQUE INDEX "Disruptions_Idx2" ON Disruptions ("Disruption_id"
COLLATE NOCASE ASC, "version" COLLATE NOCASE ASC, "category" COLLATE NOCASE
ASC, "subCategory" COLLATE NOCASE ASC);
If "version" really is an INTEGER, then it is incorrect to use version COLLATE
NOCASE in the index. NOCASE is purely for text values. This may be slowing
things down.
To solve it, in the table definition, use COLLATE NOCASE for TEXT columns and
not for INTEGER columns. Also, remove all mentions of COLLATE NOCASE in your
index definitions. Collation methods should be set in the table definition,
not in indexes, except for some unusual situations.
This should increase your speed relative to your original timings. If it slows
things down, something else I haven't spotted is wrong.
> We've found it now takes around 10% longer to do the queries than before.
That is understandable given the incorrect advice I gave you before.
In another post you report some strange timing problems with no simple
explanation. When I get such things I suspect database corruption or hardware
problems and run an integrity_check. But with a 60Gig database I might think
twice.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
