On Fri, Apr 21, 2017 at 6:21 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 21 Apr 2017, at 5:16pm, Kim Gräsman <kim.gras...@gmail.com> wrote:
>
>> Could be. Would that show up in EXPLAIN somehow?
>
> You would find it easier to spot using EXPLAIN QUERY PLAN. 'scan' means it’s
> just looking through the entire table, but you’ll see mentions of indexes
> there.
>
>> The statement I've
>> had trouble with so far is an UPDATE of a 5-million-row table.
>
> Does it have a WHERE clause ? SQLite may decide to do ordering when you use
> WHERE or ORDER BY. If there is no convenience index then it will create a
> temporary one.
>
> There are ways to minimise this, using ANALYZE and creating permanent
> indexes, but it would probably be easier to start from your WHERE clause and
> which indexes you have already created.
Thanks.
There is a WHERE clause, but EXPLAIN QUERY PLAN does not mention any indexes:
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE mytable
Also, this is just one of many (hundreds) statements, and I don't have
a good way to audit all of them. You're essentially saying that any
statement may allocate pages corresponding to an index for filtered
columns... I can see how that can get large, quickly.
Reading https://sqlite.org/tempfiles.html, it looks like these
temporary/transient indexes go into the temp store. I'm guessing the
temp store is also allocated from the private heap if
temp_store=memory?
- Kim
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
