[sqlite] BUG: Sqlite 3.6.23. Optimizer does not use indexes when a table is joined with a fts3 table

[Shopsland gmail]

### BUG: Sqlite 3.6.23. Optimizer does not use indexes when a table is
joined with a fts3 table

Given those two tables:

Table a
. number: integer primary key
. date: double (julian)
. index: dateindx (index of column date)
Total rows: 37866

Table fts (fts3 table)
. title: string
Total rows: 37866


### PROBLEM

With Sqlite 3.6.17 the following query executes in 12ms.

---------------------------------------
SELECT a.number
FROM a, fts
WHERE
a.number=fts.docid
ORDER BY a.date desc
LIMIT 20
---------------------------------------


With Sqlite version 3.6.23 the same query executes in *3238ms*.

When you join a table with a fts3 table, Sqlite 3.6.23 do not use
indexes, including the primary key.

You can partially solve this using 'indexed by'. It will work for one
index on table 'a' but not for both.

This select executes again in 12ms:

---------------------------------------
SELECT a.number
FROM a INDEXED BY dateindx, fts
WHERE
a.number=fts.docid
ORDER BY a.date desc
LIMIT 20
---------------------------------------


But if you add to this select statement a condition with 'number', the
primary key, the index for this column will not be used.

With versions 3.6.17 the following query executes in 9ms. With version
3.6.23 it executes in 175ms:

---------------------------------------
SELECT a.number
FROM a INDEXED BY dateindx, fts
WHERE
a.number=fts.docid
and a.number>1000
ORDER BY a.date desc
LIMIT 20
---------------------------------------


Thanks for this great tool.

Keep up the good work!

Greetings! :-)

Jochi Martínez
www.bfreenews.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users