sorka <sorka95...@gmail.com> wrote: > This is driving me nuts. I have two tables I'm trying to join > together on two text fields. > > CREATE TABLE tmp_role ( programId INTEGER, > roleName INTEGER, > position INTEGER, > isNew BOOL, > personId INTEGER, > name STRING); > > This table has up to a few dozen records at any one time. > > AND > > CREATE TABLE person ( > personId INTEGER PRIMARY KEY, > name text UNIQUE > ); > > This table has 10s of thousands of records. > > If I do this query: > SELECT person.ROWID FROM ApgDb.tmp_role JOIN PgDb.person ON > tmp_role.name = person.name; > > to find the ROWID of each row in person who's name matches that of > the name in tmp_role, it takes about 1 second per match....a really > long time.
The problem seems to be with the affinity of tmp_role.name column. STRING doesn't have any special meaning to SQLite, and so the column ends up with NUMERIC affinity. On the other hand, TEXT gives the column TEXT affinity. For some reason I don't quite understand, this prevents SQLite from using the index on person(name). Try this, it should run much faster: SELECT person.ROWID FROM tmp_role JOIN person ON cast(tmp_role.name as text) = person.name; For more details about data types, column affinity and such, see http://sqlite.org/datatype3.html Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
