Igor Tandetnik <itandet...@mvps.org> wrote: > 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
Ah, now I understand why the index is not used. As described in section 3 "Comparison Expressions" in the aforementioned document, when comparing NUMERIC and TEXT columns, an attempt is made to convert the text to a number, so that 12 would be considered equal to '12' and '012' and '12.0'. But when the index on person(name) was built, it interpreted these three values as strings and considered them distinct. That's why the index can't be used. Bottom line is, don't use STRING as column type, use TEXT or CHAR or similar. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
