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. However, if I instead take each of names in tmp_role and do a seperate select like this: SELECT ROWID FROM person WHERE name = "Carell|Steve"; and do it for each name, the search takes only a few ms for few dozen records in tmp_role. Now the real problem I'm trying to solve is an UPDATE like this: UPDATE tmp_role SET personId = (SELECT ROWID FROM person WHERE tmp_role.name = person.name); If I break this up into a bunch of different statements to iterate through the records in tmp_role and then execute a single statement for each name, I can accomplish this update statement fairly quickly, but as it is authored above, it's taking about 24 seconds for 24 records or about 1000 times longer than if I do it the long way :( -- View this message in context: http://old.nabble.com/Slow-JOIN-on-two-indexed-text-fields.-Why------tp26601433p26601433.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users