Have 2 tables with both one text field called term and need to run a SQL like this, to count the records in table1 where the start of term in table1 equals a term in table2:
select count(a.rowid) from table1 a inner join table2 b on (lower(b.term) = lower(substr(a.term,1,length(b.term)))) term is indexed in both tables, but not surprisingly, this query runs very slow, taking some 40 minutes. Table1 is large, maybe some 1 million rows and table2 is small, maybe some 30.000 rows. All rows in table2 are unique, but table1 has many duplicates. Any suggestions to speed this up? I could also tackle this in code rather than in SQL. RBS _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
