On Fri, Feb 28, 2014 at 6:38 AM, Bruce Sutherland <bruce.sutherl...@stiengineering.com.au> wrote: > We are tracking manufactured components with an alphanumeric serial number, > which gives us a natural key. Naturally we set type affinity TEXT on the key > column. There are many tables linked through foreign key relationships on > this serial number.
I just posted for info on this, in SO: http://stackoverflow.com/questions/22060197 :) My own question is more why is it asymmetrical, i.e. depending on which side of the join one adds a WHERE clause, the plan is indexed on both sides, or not. --DD C:\Users\DDevienne>sqlite3 SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table parent (sn text primary key, data text); sqlite> create table child_int (sn integer references parent(sn), data text, unique(sn)); sqlite> create table child_txt (sn text references parent(sn), data text, unique(sn)); sqlite> insert into parent values ('x', '1'), ('y', '2'), ('z', '3'); sqlite> insert into child_int values ('x', 'one'), ('y', 'two'), ('z', 'three'); sqlite> insert into child_txt values ('x', 'one'), ('y', 'two'), ('z', 'three'); sqlite> select p.data, c.data from parent p, child_int c on p.sn = c.sn where p.sn = 'y'; 2|two sqlite> select p.data, c.data from parent p, child_int c on p.sn = c.sn where c.sn = 'y'; 2|two sqlite> select p.data, c.data from parent p, child_txt c on p.sn = c.sn where p.sn = 'y'; 2|two sqlite> select p.data, c.data from parent p, child_txt c on p.sn = c.sn where c.sn = 'y'; 2|two sqlite> explain query plan select p.data, c.data from parent p, child_int c on p.sn = c.sn where p.sn = 'y'; 0|0|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?) 0|1|1|SEARCH TABLE child_int AS c USING INDEX sqlite_autoindex_child_int_1 (sn=?) sqlite> explain query plan select p.data, c.data from parent p, child_int c on p.sn = c.sn where c.sn = 'y'; 0|0|1|SEARCH TABLE child_int AS c USING INDEX sqlite_autoindex_child_int_1 (sn=?) 0|1|0|SCAN TABLE parent AS p sqlite> explain query plan select p.data, c.data from parent p, child_txt c on p.sn = c.sn where p.sn = 'y'; 0|0|1|SEARCH TABLE child_txt AS c USING INDEX sqlite_autoindex_child_txt_1 (sn=?) 0|1|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?) sqlite> explain query plan select p.data, c.data from parent p, child_txt c on p.sn = c.sn where c.sn = 'y'; 0|0|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?) 0|1|1|SEARCH TABLE child_txt AS c USING INDEX sqlite_autoindex_child_txt_1 (sn=?) sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
