Hello, I have been trying to solve this problem for a while and have had no
luck, so this is the first time I am trying asking for help here. I apologize
if this is not the correct place to ask such questions and would appreciate if
you would forward this to the appropriate location or let me know where to send
it.
Why does SQLite refuse to use available indexes when adding a JOIN?
To illustrate what I mean, look at the following statements to create the
database:
CREATE TABLE foo(id TEXT); CREATE INDEX `foo.index` ON foo(id); CREATE TABLE
bar(id TEXT); CREATE INDEX `bar.index` ON bar(id); CREATE VIEW baz AS SELECT id
FROM foo UNION ALL SELECT id FROM bar; CREATE TABLE bam(id TEXT, value TEXT);
INSERT INTO foo VALUES('123'); INSERT INTO foo VALUES('1123'); INSERT INTO foo
VALUES('2123'); INSERT INTO foo VALUES('3123'); INSERT INTO bar
VALUES('44123'); INSERT INTO bar VALUES('441123'); INSERT INTO bar
VALUES('442123'); INSERT INTO bar VALUES('443123');
This is the query that I try to run:
EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE
baz.id IN ('123', '234');
Here is the result of that query:
SCAN TABLE foo (~1000000 rows) SCAN TABLE bar (~1000000 rows) COMPOUND
SUBQUERIES 2 AND 3 (UNION ALL) SCAN SUBQUERY 1 (~2000000 rows) EXECUTE LIST
SUBQUERY 4 SEARCH TABLE bam USING AUTOMATIC COVERING INDEX (id=?) (~7 rows)
As you can see, instead of using an index to scan foo and bar to evaluate the
where clause, it does a full table scan.
If you want to see this / play with it, you can at SQL Fiddle:
http://sqlfiddle.com/#!7/32af2/14 (use WebSQL)
If I do the query: EXPLAIN QUERY PLAN SELECT * FROM baz WHERE baz.id IN ('123',
'234'); it does use the index to scan foo and bar.
On the other hand, the query EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM
baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN bam ON t.id=bam.id ; does
not use the index.
The context for this is that I have an unknown number of databases which are
sharded and then joined together via this view. This allows me to write queries
which don't have to worry about the attached databases and the view is simply
regenerated every time a new database needs to be added. For this reason, I
cannot manually flatten the query (because I do not know a priori which
databases will be attached).
Therefore, is there any way that I can write a query onto this view that will
join another table and still be able to use available indexes?
Thank you for your time,
Ofer Sadgat
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
