SQL related question

[yarolegovich]

As far as I understand fts4 gives no benefit when LIKE is used.

CREATE VIRTUAL TABLE item_index USING fts4(content TEXT);
...
SELECT item.*
FROM item_index
JOIN item ON (docid = item.id)
WHERE content LIKE '%' || ?1 || '%' ESCAPE '\'

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 (the first example on this page shows that a linear scan of 1.5 GB of data takes around 30 seconds using a modern PC).

[AlecKazakova]

oh yea you're totally right. It should be WHERE content MATCH ?1

[JakeWharton]

Can I interest either of you in the fame and glory that come with sending a pull request for this change?

[yarolegovich]

It should be WHERE content MATCH ?1

This will search for complete words. When inserted, text is tokenized and MATCH can perform token or token prefix matching.

I think it's better to just remove fts and continue using LIKE %query%.

Can I interest either of you in the fame and glory that come with sending a pull request for this change?

Tempting, indeed.

[AlecKazakova]

Thats true. We can prefix match but not match in the middle of a word. So typing Layout wouldn't match ConstraintLayout

We could be clever and tokenize based off of camel casing to still use an fts table for those cases. I guess the question is do we care about matching things like straint to ConstraintLayout? @JakeWharton

[JakeWharton]

No. But Layout should. I'd also like to make ConLa work eventually.

[AlecKazakova]

if we tokenize the input string on camel case then the query becomes MATCH 'con AND la' which would work.

I dont think we can build actual sqlite tokenizers through the exposed android sqlite stuff, but we can tokenize from the kotlin side and this should all theoretically work.

ConstraintLayout goes into fts as constraint layout

ConLa goes into search as con la

[JakeWharton]

I think that was the original motivation for FTS so 👍

…

On Fri, Jun 1, 2018 at 9:06 AM Alec Strong ***@***.***> wrote: if we tokenize the input string on camel case then the query becomes MATCH 'con AND la' which would work. I dont think we can build actual sqlite tokenizers through the exposed android sqlite stuff, but we can tokenize from the kotlin side and this should all theoretically work. ConstraintLayout goes into fts as constraint layout ConLa goes into search as con la — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#100 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAEEEZgg2lCylDw1r7ymgHmxsmVh2sipks5t4TxTgaJpZM4UUzRv> .