Hi David,
Thanks for your thoughtful reply.
Can't go into as much detail as you. But a couple comments.
"primary key unique" is redundant, and will actually create a
redundant unique index.
Are you refering to the CREATE TABLE clauses for the "items" and
"registers" tables?
I appear to have indexes named "sqlite_autoindex_items_1",
"sqlite_autoindex_items_2" (for the blob column) and
"sqlite_autoindex_registers_1".
If I run
CREATE TABLE "items2" ("item-id" INTEGER PRIMARY KEY NOT NULL , "blob"
BLOB NOT NULL UNIQUE )
...then I just get "sqlite_auto_index_items2_1" for the blob colum.
Thanks for that: it's a good find! I'll do the same for the "registers"
table as well.
"Do later version of SQLite manage to spot the constant
propagation opportunity?"
This requires way more algebra and proof than I think you
realize. "entrys" is on the right side of a LEFT OUTER JOIN, and
therefore may be null when it comes time to do the next OUTER
JOIN to "entry-items", so a direct replacement of an "equals"
constraint isn't possible. And after that it again starts
becoming algebra as to whether that null can affect things etc.
Yes, you are right. I could use the inner join for the "entrys" join and
the "items" join but not the "entry-items" join because each entry can have
more than one item.
Changing the "entrys" join to an INNER JOIN and adding both ORDER BY
clauses gets me a much better query plan:
-----
1|0|0|SEARCH TABLE entrys USING COVERING INDEX
entrys-log-id-region-key-entry-number (log-id=? AND region=?)
0|0|1|SEARCH TABLE entrys USING INDEX entrys-log-id-region-key-entry-number
(log-id=? AND region=?)
0|1|0|SEARCH SUBQUERY 1 AS specific-entrys USING AUTOMATIC COVERING INDEX
(key=?)
0|2|2|SEARCH TABLE entry-items USING COVERING INDEX
entry-items-log-id-entry-number-item-id (log-id=? AND entry-number=?)
0|3|3|SEARCH TABLE items USING INTEGER PRIMARY KEY (rowid=?)
-----
Changing the "items" join to an INNER JOIN doesn't get me any more but I'll
do it anyway as the OUTER JOIN is not strictly needed.
Thanks for that!
I started with an OUTER JOIN as I find it easier to show that it's doing
the correct thing because I can search the output for errant NULLs. Trying
to detect missing rows in an INNER JOIN is harder. I then failed to realise
that an INNER JOIN would suffice.
It now even does the constant propagation of the log-id at (** 5 **) but
not the "entry-number" one!
For the ordering, I recommend seeing if you can replace one of
those "entrys" indexes so that they start with "key" as the
first field in the index. That would at least give it more
opportunity to use that index for the ordering rather than
needing to order things after they're all collected. That and
explicitly stating the order by in _both_ the sub select and the
final might make it notice "I can use this _ordered_ sub select
as the outer table in the joins and get my overall ordering that
way"... or it may not. Worth a try though.
Yeah. I've had a fiddle around and can't get it to do it. However,
converting to an INNER JOIN as above seems to get it to work without
changing the indexes tho'.
In the schema I have,
CREATE UNIQUE INDEX "entrys-log-id-region-key-entry-number" ON "entrys" (
"log-id" ASC, "region" ASC, "key" ASC, "entry-number" ASC)
...which seems to get used for the sub-query. It gives it its ordering and
the "key" part also gets used when I add the extra WHERE constraint on
"key" (** 2**) so it seems to be having the effect you mention.
If I add an ORDER BY clause to the sub-select (** 3 **) the query plan
doesn't change so I guess it's happy to use that index to get the ordering.
If I then also add an ORDER BY to the main select it still uses a temporary
b-tree to confirm the sort. This is the main source of my confusion because
the query plan shows that the joins are all executed as inner loops so the
ordering of the outer query should be preserved
Thanks for your help David and thanks for solving the problem!
Regards,
@ndy
--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
