On 1/21/16, Denis Burke <burkeden at gmail.com> wrote:
> I have a DB with a large table (1M records) and several small tables (~100
> records).
>
> I am running queries like this:
>
> select F1 from BigTable inner join SmallTable on BigTable.F2=SmallTable.F2
> where SmallTable.F3=1 and BigTable.F4=2
>
> That query takes 4 minutes to run and the query plan shows it is scanning
> SmallTable and then searching BigTable using an index that adds almost no
> value (it is a compound index with the only available column having the
> same value for over 99% of records, even though there is an index on F4
> which would dramatically cut down the records it needs to look at).
>
> I have both run analyze and even re-indexed.
Re-indexing should not change anything. But I'm surprised that
ANALYZE did not fix the problem.
Can you please run ".fullschema" on your database from the sqlite3.exe
command line shell and post the output
sqlite3 your-database.db .fullschema
In addition to showing all tables and indexes, the ".fullschema"
command also shows the content of the sqlite_stat1 table, which is the
result of running ANALYZE. If nothing else, that will enable us to
reproduce the problem and try to find a solution.
--
D. Richard Hipp
drh at sqlite.org
