mateusz herych content search problem on android
DESCRIPTION
TRANSCRIPT
Mateusz Herych
Android Developer - Base CRMCo-organizer - GDG KrakowCo-organizer - KrakDroid
Stats
LIKE ‘%smth%’
LIKE ‘%smth%’is not the way.
Search
SearchOffline.
Why?
Why?Let the backend guys do the job
Why?Internet is not everywhere.
Why?Internet is not everywhere.It takes time. (especially SSL)
Why?Internet is not everywhere.It takes time. (especially SSL)And sometimes it’s shitty.
Why?Internet is not everywhere.It takes time. (especially SSL)And sometimes it’s shitty.
Sure, some apps don’t really need it
You need an Internet to order that taxi anyway
Do you keep offline content?
Let your users navigate fast.
Did I say fast?
How?Let’s go deeper.
Context
CRM- Contacts- Deals- Notes- ...
CRM- Contacts (~100)- Deals (~50)- Notes (~100)- ... 2011
select id from deals where name LIKE ‘%something%’
CRM- Contacts (~40K)- Deals (~20K)- Notes (~300K)- ...
HOW DOES “LIKE” WORKS LIKE?
Docs saying
I tried to put all the conditions that need to be satisfied so SQLite can use indices combined with LIKE
operator.
Docs saying
They didn’t fit.
Docs saying
http://www.sqlite.org/optoverview.html
Docs saying
Hey, you, SQLite!
EXPLAIN (my) QUERY PLAN
PRAGMA case_sensitive_like=1;
PRAGMA case_sensitive_like=1;CREATE INDEX search_index on deals(name);
PRAGMA case_sensitive_like=1;CREATE INDEX search_index on deals(name);SELECT id FROM deals WHERE name LIKE ‘Some%’;
EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘Some%’;
SEARCH TABLE deals USING COVERING INDEX search_index (name>? AND name<?) (~31250 rows)
EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘%Some%’;
EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘%Some%’;
SCAN TABLE deals (~500000 rows)
EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘%Some%’;
SCAN TABLE deals (~500000 rows)
(And then you die)
first_name || ‘ ‘ || last_name?complicated queries, VIEWs?
Like is NOT the way to go.
What people think SQLite is
What SQLite really is
SQLite is powerful
Not kidding.
FTS3Full Text Search
CREATE VIRTUAL TABLE search USING fts3 (tokens)
?
CREATE VIRTUAL TABLE search USING fts3 (tokens INT)
Nope.
PRAGMA table_info(search);cid|name|type|notnull|dflt_value|pk0|word||0||0
All is TEXT, except for hidden rowid.
What is virtual table?Imagine it’s a Java interface.interface VirtualTable { void insert(Params p); void update(Params p); // etc, also createTable.}
What is a virtual table?
class Fts3 implements VirtualTable { // …}
MATCHLet’s go make some magic.
SELECT * FROM search WHERE content MATCH ‘something’
SELECT rowid, * FROM search WHERE content MATCH ‘something’rowid|word1|something2|not something special3|SoMeThInG
SELECT rowid, * FROM search WHERE contentMATCH ‘some* spe*’
rowid|word2|not something special
CREATE VIRTUAL TABLE search USING fts3 (author, lyrics)
SELECT * FROM search WHERE lyrics MATCH ‘author:Giorgio Synthesizer
author |lyricsGiorgio Moroder|..Why don’t I use a synthesizer...
Cool?
Cool?Look at this.
SELECT * FROM search WHERE lyrics MATCH ‘why NEAR synthesizer’
author |lyricsGiorgio Moroder|..Why don’t I use synthesizer...
SELECT * FROM search WHERE lyrics MATCH ‘why NEAR/3 synthesizer’
author |lyricsGiorgio Moroder|..Why don’t I use synthesizer...
Tips.
1. Your FTS vtable should contain only tokens. Divided into sections.
2. Link your FTS table’s records with other table (containing real object’s id and type) using rowid.
3. Remember. FTS is fast enough for searching purposes. But it’s always slower than ‘=’ based query on indexed field.
4. EXPLAIN QUERY PLAN doesn’t work for fts tables. Try to measure it with .timer ON.
5. ???
6. QUESTIONS TIME!