Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Mateusz Herych - LIKE '%smth%' is not the way

Base Lab
February 12, 2014

Mateusz Herych - LIKE '%smth%' is not the way

Droidcon IT, Turin Feb 2014

Base Lab

February 12, 2014
Tweet

More Decks by Base Lab

Other Decks in Programming

Transcript

  1. Sure, some apps don’ t really need it You need

    an Internet to order that taxi anyway
  2. I tried to put all the conditions that need to

    be satisfied so SQLite can use indices combined with LIKE operator. Docs saying
  3. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE

    ‘Some%’; SEARCH TABLE deals USING COVERING INDEX search_index (name>? AND name<?) (~31250 rows)
  4. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE

    ‘%Some%’; SCAN TABLE deals (~500000 rows)
  5. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE

    ‘%Some%’; SCAN TABLE deals (~500000 rows) (And then you die)
  6. What is virtual table? Imagine it’s a Java interface. interface

    VirtualTable { void insert(Params p); void update(Params p); // etc, also createTable. }
  7. SELECT rowid, * FROM search WHERE content MATCH ‘something’ rowid|word

    1|something 2|not something special 3|SoMeThInG
  8. SELECT * FROM search WHERE lyrics MATCH ‘author:Giorgio Synthesizer author

    |lyrics Giorgio Moroder|..Why don’t I use a synthesizer...
  9. SELECT * FROM search WHERE lyrics MATCH ‘why NEAR synthesizer’

    author |lyrics Giorgio Moroder|..Why don’t I use synthesizer...
  10. SELECT * FROM search WHERE lyrics MATCH ‘why NEAR/3 synthesizer’

    author |lyrics Giorgio Moroder|..Why don’t I use synthesizer...
  11. 2. Link your FTS table’s records with other table (containing

    real object’s id and type) using rowid.
  12. 3. Remember. FTS is fast enough for searching purposes. But

    it’s always slower than ‘=’ based query on indexed field.