[SOLVED] Can I use AND statements in FTS Query?

Can I use AND statements in FTS Query?

Is is possible to still enjoy the benefits of FTS3/4 in SQLite while executing a query with AND statements on indexed columns in a virtual table? Example:

SELECT title, ID, creationDate FROM documents WHERE type=1 AND status=2 AND searchableFields MATCHES '"john doe"';

SELECT title, ID, creationDate FROM documents WHERE type=1 AND status=2 AND searchableFields CONTAINS 'john doe';

Where the columns type and status are indexed in the virtual table.

Solution

All columns in an FTS table are text columns, and FTS-indexed; the documentation says:

If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose.

You should not do searches like type=1 on an FTS table:

FTS tables can be queried efficiently using SELECT statements of two different forms:

Query by rowid. If the WHERE clause of the SELECT statement contains a sub-clause of the form "rowid = ?", where ? is an SQL expression, FTS is able to retrieve the requested row directly using the equivalent of an SQLite INTEGER PRIMARY KEY index.

Full-text query. If the WHERE clause of the SELECT statement contains a sub-clause of the form " MATCH ?", FTS is able to use the built-in full-text index to restrict the search to those documents that match the full-text query string specified as the right-hand operand of the MATCH clause.

If neither of these two query strategies can be used, all queries on FTS tables are implemented using a linear scan of the entire table. If the table contains large amounts of data, this may be an impractical approach.

You should not think of an FTS table as a table, but as an index.

If you want to store non-text data, you should use a separate, normal table, and query it separately:

SELECT title, ID, creationDate
FROM documents
WHERE type=1
  AND status=2
  AND ID IN (SELECT docid
             FROM documents_FTS
             WHERE searchableFields MATCH '"john doe"');

To avoid storing the same data twice, you can use contentless or external content tables.