Thank you!!!!! That was it. I've been pulling my hair out over this all day. I should have seen it. I've never used STRING in my own tables and I inherited this from someone else and didn't even think twice that the type difference would be the issue.
Thanks you again. sorka 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 tmp_role JOIN 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------tp26601433p26602612.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
