Kurt Welgehausen wrote: >> ... only the first index is opened ... > > That's right. SQLite uses only one index, and it doesn't keep > statistics, so it may pick the wrong index if it has to choose. > Try forcing it to use the other index by dropping the one that > it's using now. > > Changing 'table1, table2 where' to 'table1 join table2 on' is > not going to help. >
You can also get it to use the other index by reversing the order the tables are joined. Ie. replace parole JOIN mega with mega JOIN parole The logic of this statement is basically to scan through every record in one of the tables and find all the records in the other table that have the same value for the word field. The index is used to locate the macthing values in the second table. Every pair of matching values is returned. If the same words are repeated in the parole table (with 6M records) and are unique in the mega table (with 200K records) it should make no difference. If each parole record matches to one mega record, then there should be an average of 30 parole records for each mega record. So you have 6M outer loops with 1 indexed lookup each, or 200K outer loops with an average of 30 indexed lookups each, ie. 6M lookups either way. It might be faster to scan the smaller mega table and use the index to find matching records in the larger parole table. Do you really want 6M result records? If my assumptions are not correct, you should order the join so that the size of the first table multiplied by the average number of matching records in the other table is as small as possible.
