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
140
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
100
Slawek Skowron - Monitoring @ Scale
baselab
0
130
Karol Nowak - Monitoring clock drift in Amazon EC2 environment
baselab
0
110
Tomasz Nowak - Web Application Testing made easy
baselab
0
290
Szymon Pawlik - UX i Automatyzacja czyli jak testerzy mogą poprawić produkt.
baselab
0
240
Jerzy Chałupski - Offline mode in Android apps
baselab
3
480
Jerzy Chałupski - Data model on Android
baselab
4
230
Other Decks in Programming
See All in Programming
SQLアンチパターン第2版 データベースプログラミングで陥りがちな失敗とその対策 / Intro to SQL Antipatterns 2nd
twada
PRO
36
11k
バイブコーディングの正体——AIエージェントはソフトウェア開発を変えるか?
stakaya
5
730
リバースエンジニアリング新時代へ! GhidraとClaude DesktopをMCPで繋ぐ/findy202507
tkmru
7
1.7k
Vibe Codingの幻想を超えて-生成AIを現場で使えるようにするまでの泥臭い話.ai
fumiyakume
21
10k
変化を楽しむエンジニアリング ~ いままでとこれから ~
murajun1978
0
670
大規模FlutterプロジェクトのCI実行時間を約8割削減した話
teamlab
PRO
0
450
Gemini CLIの"強み"を知る! Gemini CLIとClaude Codeを比較してみた!
kotahisafuru
3
920
decksh - a little language for decks
ajstarks
4
21k
「次に何を学べばいいか分からない」あなたへ──若手エンジニアのための学習地図
panda_program
3
710
ソフトウェア設計とAI技術の活用
masuda220
PRO
25
7.3k
TypeScriptでDXを上げろ! Hono編
yusukebe
4
930
NEWT Backend Evolution
xpromx
1
170
Featured
See All Featured
4 Signs Your Business is Dying
shpigford
184
22k
The Illustrated Children's Guide to Kubernetes
chrisshort
48
50k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
53
2.9k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
21k
Building Applications with DynamoDB
mza
95
6.5k
Building Adaptive Systems
keathley
43
2.7k
GraphQLとの向き合い方2022年版
quramy
49
14k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
50
5.5k
RailsConf 2023
tenderlove
30
1.2k
How STYLIGHT went responsive
nonsquared
100
5.7k
Being A Developer After 40
akosma
90
590k
The Pragmatic Product Professional
lauravandoore
36
6.8k
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!