On additional thing. One of the indices will not be required. Since one table will be the driving table and will require a full scan. The other table should have the index. I would make that the smaller of the tables.
Secondly if at all possible try not to make composit fields. A compoisit field is one where you have one field but put two or more data elements in it. For example a "name" that you use to store first and last names vs creating two columns first_name last_name. --- On Sat, 9/12/09, RB Smissaert <bartsmissa...@blueyonder.co.uk> wrote: > From: RB Smissaert <bartsmissa...@blueyonder.co.uk> > Subject: Re: [sqlite] Any way to speed up this SQL? > To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> > Date: Saturday, September 12, 2009, 6:47 AM > Thanks for the tip and will have a > look at that. > I have in the meantime made this a lot faster by making the > data in both > tables upper case and making the small table smaller by > taking out invalid > records. I can then run a simpler join with glob, although > I noticed it > still doesn't use the index. Sounds like your solution will > be faster still. > > RBS > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of marbex > Sent: 12 September 2009 12:25 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Any way to speed up this SQL? > > > I had a similar issue. I wanted to find strings that > started as another > string in the same table and field. My solution was to > create a temp table > that consisted of the id, the string and the first word of > the string which > I then indexed. The table had 30 000 records and the total > processing time > went down from 15-20 minutes to 15 seconds! > > Applying that solution to your case, not knowing the nature > of your data (I > had names), I guess you can do something like this: > > - Get the length of the shortest string in table2.term. > Lets say it's 3. > > - Create a tmptable of table1 > Create temp tmptable1 as > select term, lower(substr(term,1,3)) shortest > from table1 > > - Create a tmptable of table2 > Create temp tmptable2 as > select term, lower(substr(term,1,3)) shortest > from table2 > > - Index the shortest fields > Create index idx_tmptable1_shortest on tmptable1(shortest) > Create index idx_tmptable2_shortest on tmptable2(shortest) > > -Run this sql > select > count(a.rowid) > from tmptable1 a inner join tmptable2 b on > a.shortest=b.shortest > where (lower(b.term) = > lower(substr(a.term,1,length(b.term)))) > > > > RB Smissaert wrote: > > > > 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 > > > > > > -- > View this message in context: > http://www.nabble.com/Any-way-to-speed-up-this-SQL--tp25412299p25413614.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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
