How should I up a temp table used to search for matches in a larger table?

Table A has millions of rows of indexed phrases (1-5 words). I'm looking for matches to about 20-30 phrases, e.g., ('bird', 'cat', 'cow', 'purple rain', etc.). I know that the IN operator is generally a bad idea when the search set is large - so the solution is to create a temp table (in me开发者_Python百科mory) and JOIN it against the table I'm looking for.

I can create a TEMP TABLE B using my search phrases, and I know that if I do the join, the SQL engine will work against the Table A indices. Does it make any difference at all to index TEMP TABLE B phrases?

Edit... I just realized you're asking about sqlite. I'd say the same principal of keeping the very small joined table in cache would still apply though.

When joining tables, SQL server will put the relevant contents of one table in cache, if possible. Your 20 to 30 phrases will certainly fit in cache, so there would really be no point of indexing. Indexing is useful for looking up values, but SQL server will already have these values in cache. Also, since SQL server reads data a page at a time (a page is 8K), it will be able to read that entire table in one read.

When you make your temp table, make sure to use the same datatype so SQL server doesn't have to convert values to match.

Why would IN be a bad idea when the search terms are many?

From what I understand when I read about the SQLite query planner, a list of IN(1,2,3,4,5,6,N) would generate the same query plan as a join against a temporary table with the same rows.

An index on a temporary search term table will not make the query any faster since you process all terms. Going via index only adds processing time.