Ofer Sadgat wrote:
> Why is SQLite refusing to use available indexes when adding a JOIN?
>
> CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar;
>
> SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE baz.id IN ('123',
> '234');
In the current SQLite version, the compound query using UNION ALL
confuses the query optimizer.
> SQL Fiddle: http://sqlfiddle.com/#!7/32af2/14 (use WebSQL)
WebSQL uses whatever random SQLite version your browser has.
With SQL.js (using SQLite 3.7.17), the indexes _are_ used.
The following query would use the indexes in any version:
SELECT foo.id FROM foo LEFT JOIN ...
UNION ALL
SELECT bar.id FROM bar LEFT JOIN ...;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
