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

Droidcon IT, Turin Feb 2014

2. Mateusz Herych Android Developer - Base CRM Co-organizer - GDG Krakow Co-organizer - KrakDroid
3. Stats
4. LIKE ‘%smth%’
5. LIKE ‘%smth%’ is not the way.
6. Search
7. Search Offline.
8. Why?
9. Why? Let the backend guys do the job
10. Why? Internet is not everywhere.
11. Why? Internet is not everywhere. It takes time. (especially SSL)
12. Why? Internet is not everywhere. It takes time. (especially SSL) And sometimes it’s shitty.
13. Why? Internet is not everywhere. It takes time. (especially SSL) And sometimes it’s shitty.
14. Sure, some apps don’ t really need it You need an Internet to order that taxi anyway
15. Do you keep offline content? Let your users navigate fast.
16. Did I say fast?
17. How? Let’s go deeper.
18. Context
19. CRM - Contacts - Deals - Notes - ...
20. CRM - Contacts (~100) - Deals (~50) - Notes (~100) - ... 2009
21. select id from deals where name LIKE ‘% something%’
22. CRM - Contacts (~40K) - Deals (~20K) - Notes (~300K) - ...
24. HOW DOES “LIKE” WORKS LIKE?
25. Docs saying
26. I tried to put all the conditions that need to be satisfied so SQLite can use indices combined with LIKE operator. Docs saying
27. They didn’t fit. Docs saying
28. http://www.sqlite. org/optoverview.html Docs saying
29. Hey, you, SQLite! EXPLAIN (my) QUERY PLAN
30. PRAGMA case_sensitive_like=1;
31. PRAGMA case_sensitive_like=1; CREATE INDEX search_index on deals(name);
32. PRAGMA case_sensitive_like=1; CREATE INDEX search_index on deals(name); SELECT id FROM deals WHERE name LIKE ‘Some%’;
33. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘Some%’; SEARCH TABLE deals USING COVERING INDEX search_index (name>? AND name<?) (~31250 rows)
34. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘%Some%’;
35. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘%Some%’; SCAN TABLE deals (~500000 rows)
36. EXPLAIN QUERY PLAN SELECT id FROM deals WHERE name LIKE ‘%Some%’; SCAN TABLE deals (~500000 rows) (And then you die)
37. first_name || ‘ ‘ || last_name? UNIONs, complicated VIEWs? Like is NOT the way to go.
38. What people think SQLite is
39. What SQLite really is
40. SQLite is powerful Not kidding.
41. FTS3 Full Text Search
42. CREATE VIRTUAL TABLE search USING fts3 (tokens)
43. ? CREATE VIRTUAL TABLE search USING fts3 (tokens INT)
44. Nope. PRAGMA table_info(search); cid|name|type|notnull|dflt_value|pk 0|word||0||0
45. All is TEXT, except for hidden rowid.
46. What is virtual table? Imagine it’s a Java interface. interface VirtualTable { void insert(Params p); void update(Params p); // etc, also createTable. }
47. What is a virtual table? class Fts3 implements VirtualTable { // … }
49. MATCH Let’s go make some magic.
50. SELECT * FROM search WHERE content MATCH ‘something’
51. SELECT rowid, * FROM search WHERE content MATCH ‘something’ rowid|word 1|something 2|not something special 3|SoMeThInG
52. SELECT rowid, * FROM search WHERE content MATCH ‘some* spe*’ rowid|word 2|not something special
53. CREATE VIRTUAL TABLE search USING fts3 (author, lyrics)
54. SELECT * FROM search WHERE lyrics MATCH ‘author:Giorgio Synthesizer author |lyrics Giorgio Moroder|..Why don’t I use a synthesizer...
55. Cool?
56. Cool? Look at this.
57. SELECT * FROM search WHERE lyrics MATCH ‘why NEAR synthesizer’ author |lyrics Giorgio Moroder|..Why don’t I use synthesizer...
58. SELECT * FROM search WHERE lyrics MATCH ‘why NEAR/3 synthesizer’ author |lyrics Giorgio Moroder|..Why don’t I use synthesizer...
59. Tips.
60. 1. Your FTS vtable should contain only tokens. Eventually divided into sections.
61. 2. Link your FTS table’s records with other table (containing real object’s id and type) using rowid.
62. 3. Remember. FTS is fast enough for searching purposes. But it’s always slower than ‘=’ based query on indexed field.
63. 4. EXPLAIN QUERY PLAN doesn’t work for fts tables. Try to measure it with .timer ON.
64. 5. ???
65. 6. QUESTIONS TIME!