Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Mateusz Herych - LIKE '%smth%' is not the way
Search
Base Lab
February 12, 2014
Programming
0
130
Mateusz Herych - LIKE '%smth%' is not the way
Droidcon IT, Turin Feb 2014
Base Lab
February 12, 2014
Tweet
Share
More Decks by Base Lab
See All by Base Lab
Szymon Sobczak - Hadoop + Storm
baselab
0
93
Slawek Skowron - Monitoring @ Scale
baselab
0
110
Karol Nowak - Monitoring clock drift in Amazon EC2 environment
baselab
0
100
Tomasz Nowak - Web Application Testing made easy
baselab
0
280
Szymon Pawlik - UX i Automatyzacja czyli jak testerzy mogą poprawić produkt.
baselab
0
240
Jerzy Chałupski - Offline mode in Android apps
baselab
3
470
Jerzy Chałupski - Data model on Android
baselab
4
210
Other Decks in Programming
See All in Programming
Increased Performance and Developer Productivity with Jakarta EE 11
ivargrimstad
0
380
実践!難読化ガイド
mitchan
0
210
Shinjuku.rb#95:心の技術書紹介
free_world21
1
110
LangGraphでのHuman-in-the-Loopの実装
os1ma
3
1.1k
rails_girls_is_my_gate_to_join_the_ruby_commuinty
maimux2x
0
200
ドメイン駆動設計を実践するために必要なもの
bikisuke
4
330
意外とフォントが大事だった話 / Font Issues on Internationalization
fumi23
0
110
Scala アプリケーションのビルドを改善してデプロイ時間を 1/4 にした話 | How I improved the build of my Scala application and reduced deployment time by 4x
nomadblacky
1
180
全部見せます! クラシルリワードのSwiftTesting移行プロジェクト
uetyo
0
210
Rubyのobject_id
qnighy
6
1.3k
サーバーレスで負荷試験!Step Functions + Lambdaを使ったk6の分散実行
shuntakahashi
6
1.6k
Rechartsで楽にゴリゴリにカスタマイズする!
10tera
1
170
Featured
See All Featured
The World Runs on Bad Software
bkeepers
PRO
64
11k
Speed Design
sergeychernyshev
22
430
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
89
16k
The Power of CSS Pseudo Elements
geoffreycrofte
71
5.3k
A Philosophy of Restraint
colly
202
16k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
131
32k
Creatively Recalculating Your Daily Design Routine
revolveconf
215
12k
GraphQLの誤解/rethinking-graphql
sonatard
65
9.8k
Robots, Beer and Maslow
schacon
PRO
157
8.2k
Typedesign – Prime Four
hannesfritz
39
2.3k
A better future with KSS
kneath
235
17k
VelocityConf: Rendering Performance Case Studies
addyosmani
322
23k
Transcript
None
Mateusz Herych Android Developer - Base CRM Co-organizer - GDG
Krakow Co-organizer - KrakDroid
Stats
LIKE ‘%smth%’
LIKE ‘%smth%’ is not the way.
Search
Search Offline.
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)
- ... 2009
select id from deals where name LIKE ‘% something%’
CRM - Contacts (~40K) - Deals (~20K) - Notes (~300K)
- ...
None
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? UNIONs, complicated VIEWs? Like
is NOT the way to go.
What people think SQLite is
What SQLite really is
SQLite is powerful Not kidding.
FTS3 Full 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|pk 0|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 {
// … }
None
MATCH Let’s go make some magic.
SELECT * FROM search WHERE content MATCH ‘something’
SELECT rowid, * FROM search WHERE content MATCH ‘something’ rowid|word
1|something 2|not something special 3|SoMeThInG
SELECT rowid, * FROM search WHERE content MATCH ‘some* spe*’
rowid|word 2|not something special
CREATE VIRTUAL TABLE search USING fts3 (author, lyrics)
SELECT * FROM search WHERE lyrics MATCH ‘author:Giorgio Synthesizer author
|lyrics Giorgio Moroder|..Why don’t I use a synthesizer...
Cool?
Cool? Look at this.
SELECT * FROM search WHERE lyrics MATCH ‘why NEAR synthesizer’
author |lyrics Giorgio Moroder|..Why don’t I use synthesizer...
SELECT * FROM search WHERE lyrics MATCH ‘why NEAR/3 synthesizer’
author |lyrics Giorgio Moroder|..Why don’t I use synthesizer...
Tips.
1. Your FTS vtable should contain only tokens. Eventually 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!