Introduction to Searching with Solr

Transcript

1. Introduction to Searching with Solr Krzysztof Skarbek October 30, 2015
2. Searching
3. Searching • Most of us start browsing the Internet with a search engine – that's how natural searching is
4. Searching • Most of us start browsing the Internet with a search engine – that's how natural searching is • Unless your site has very little information, or everything is reachable with a few clicks, searching becomes a vital feature
5. Searching • Poor man's solution:
6. Searching • Poor man's solution:
7. Searching • Poor man's solution: • You have to expose the data to Google
8. Searching • Poor man's solution: • You have to expose the data to Google • Guess what? Ads
9. Searching in examples
10. Searching in examples • We'll demonstrate and compare SQL database searches and searching with Solr
11. Searching in examples • We'll demonstrate and compare SQL database searches and searching with Solr • We'll use the same example database in all cases
12. Example database
13. Example database • Extracted from a real-life web application
14. Example database • Extracted from a real-life web application • Contains blog posts and culinary recipes scraped from cooking blogs
15. Example database • Extracted from a real-life web application • Contains blog posts and culinary recipes scraped from cooking blogs • 22 tables, 69300 records in posts table, 550 MB of textual data
16. Example database blogs id name url ... blogs id name url ...
17. Example database posts id blog_id title body ... posts id blog_id title body ... blogs id name url ... blogs id name url ...
18. Example database posts id blog_id title body ... posts id blog_id title body ... recipes id post_id title ... recipes id post_id title ... blogs id name url ... blogs id name url ...
19. Example database posts id blog_id title body ... posts id blog_id title body ... recipes id post_id title ... recipes id post_id title ... ingredients id name ... ingredients id name ... recipes_ingredients recipe_id ingredient_id amount ... recipes_ingredients recipe_id ingredient_id amount ... blogs id name url ... blogs id name url ...
20. Naïve approach with SQL LIKE
21. Naïve approach with SQL LIKE • SELECT posts.id FROM recipes INNER JOIN posts ON posts.id = recipes.post_id INNER JOIN blogs ON blogs.id = posts.blog_id WHERE posts.title LIKE '%cake%' OR posts.body LIKE '%cake%' OR recipes.title LIKE '%cake%';
22. Naïve approach with SQL LIKE • SELECT posts.id FROM recipes INNER JOIN posts ON posts.id = recipes.post_id INNER JOIN blogs ON blogs.id = posts.blog_id WHERE posts.title LIKE '%cake%' OR posts.body LIKE '%cake%' OR recipes.title LIKE '%cake%'; 949 rows in set (10.07 sec)
23. Naïve approach with SQL LIKE • Slow
24. Naïve approach with SQL LIKE • Slow • Very bad effect on database performance
25. Naïve approach with SQL LIKE • Slow • Very bad effect on database performance • Problems with whitespace/punctuation (e.g.: LIKE '% cake %' against “cake!”)
26. Naïve approach with SQL LIKE • Slow • Very bad effect on database performance • Problems with whitespace/punctuation (e.g.: LIKE '% cake %' against “cake!”) • Really, a bad idea
27. Searching features built into RDBMS • MySQL – full text search indexes
28. Searching features built into RDBMS • MySQL – full text search indexes CREATE TABLE posts ( id INT NOT NULL AUTO INCREMENT, title VARCHAR(200), body TEXT, ... FULLTEXT (title, body), ...
29. Searching features built into RDBMS SELECT posts.id FROM recipes INNER JOIN posts ON posts.id = recipes.post_id INNER JOIN blogs ON blogs.id = posts.blog_id WHERE MATCH (posts.title, posts.body) AGAINST ('cake') OR MATCH (recipes.title) AGAINST ('cake');
30. Searching features built into RDBMS SELECT posts.id FROM recipes INNER JOIN posts ON posts.id = recipes.post_id INNER JOIN blogs ON blogs.id = posts.blog_id WHERE MATCH (posts.title, posts.body) AGAINST ('cake') OR MATCH (recipes.title) AGAINST ('cake'); 1042 rows in set (0.22 sec)
31. Searching features built into RDBMS • Much faster
32. Searching features built into RDBMS • Much faster • But still not blazingly fast
33. Searching features built into RDBMS • Much faster • But still not blazingly fast • Used to only be available for MyISAM tables
34. Searching features built into RDBMS • Much faster • But still not blazingly fast • Used to only be available for MyISAM tables (not anymore with MySQL 5.6)
35. Searching features built into RDBMS • Similar features in other RDBMS: – PostgreSQL: Full Text Searching – MSSQL: Full-Text Search – Oracle: Oracle Text – SQLite: FTS5 extension
36. Enter Solr
37. Enter Solr • Based on Lucene, a high-performance, full- featured text search engine library
38. Enter Solr • Based on Lucene, a high-performance, full- featured text search engine library • Notable features:
39. Enter Solr • Based on Lucene, a high-performance, full- featured text search engine library • Notable features: – ranked searching
40. Enter Solr • Based on Lucene, a high-performance, full- featured text search engine library • Notable features: – ranked searching – flexible faceting, highlighting, joins and result grouping
41. Enter Solr • Based on Lucene, a high-performance, full- featured text search engine library • Notable features: – ranked searching – flexible faceting, highlighting, joins and result grouping – allows simultaneous update and searching
42. Enter Solr • Based on Lucene, a high-performance, full- featured text search engine library • Notable features: – ranked searching – flexible faceting, highlighting, joins and result grouping – allows simultaneous update and searching – support for filters
43. How Solr works
44. How Solr works • Importing data from a source (e.g. SQL database)
45. How Solr works • Importing data from a source (e.g. SQL database) – full import
46. How Solr works • Importing data from a source (e.g. SQL database) – full import – delta import
47. How Solr works • Importing data from a source (e.g. SQL database) – full import – delta import • Indexing imported data (with index filters)
48. How Solr works • Importing data from a source (e.g. SQL database) – full import – delta import • Indexing imported data (with index filters) • Searching (with query filters)
49. Solr configuration
50. Solr configuration • Brace yourselves, XML is coming
51. Solr configuration <dataConfig> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://host:3306/database" user="username" password="hunter1" batchSize="-1" /> ... </dataConfig>
52. Solr configuration <document name="blogs_solr"> <entity name="item" query=" SELECT r.id AS id, r.post_id AS post_id, r.title AS recipe_title, ... FROM recipes AS r INNER JOIN posts AS p ON p.id = r.post_id INNER JOIN blogs AS b ON b.id = p.blog_id ORDER BY posted_on DESC "
53. Solr configuration deltaQuery=" SELECT r.id AS id, r.post_id AS post_id, r.title AS recipe_title, ... FROM recipes AS r INNER JOIN posts AS p ON p.id = r.post_id INNER JOIN blogs AS b ON b.id = p.blog_id WHERE p.added_on > '${dataimporter.last_index_time}' ORDER BY posted_on DESC "
54. Solr configuration <entity name=”item”> <field column="id" name="id" /> <field column="post_id" name="post_id" /> <field column="recipe_title" name="recipe_title" /> ... <entity name="ingredients" query="SELECT i.name AS ingredient_name FROM ingredients AS i ... WHERE ri.recipe_id = '${item.id}'"> <field column="ingredient_name" name="ingredients" />
55. Solr in action
56. Solr in action • Delta import: curl http://host:port/solr/database/dataimport \ --data 'command=delta-import'
57. Solr in action • Delta import: • Search: curl http://host:port/solr/database/dataimport \ --data 'command=delta-import' curl http://host:port/solr/database/select? q=post_body%3Acake
58. Solr in action { "responseHeader": { ... "QTime": 2, "params": { ... "q": "post_body:cake", ... } }, "response": { "numFound": 1009, ...
59. Solr in action { "responseHeader": { ... "QTime": 2, "params": { ... "q": "post_body:cake", ... } }, "response": { "numFound": 1009, ... Blazingly fast
60. Using Solr in application code
61. Using Solr in application code • As simple as making an HTTP request: $phrase = 'cake'; $url = 'http://host/solr/database/select?q=' . urlencode('post_body:' . $phrase); $data = json_decode(file_get_contents($url), TRUE);
62. Using Solr in application code • There are client libraries/language bindings available for all popular languages
63. Filters
64. Filters • Transform and normalize the search data
65. Filters • Transform and normalize the search data • Examples:
66. Filters • Transform and normalize the search data • Examples: – LowerCaseFilterFactory – lower case tokens
67. Filters • Transform and normalize the search data • Examples: – LowerCaseFilterFactory – lower case tokens – ASCIIFoldingFilterFactory – converts >127 unicode characters into their ASCII equivalents
68. Filters • Transform and normalize the search data • Examples: – LowerCaseFilterFactory – lower case tokens – ASCIIFoldingFilterFactory – converts >127 unicode characters into their ASCII equivalents – External: MorfologikFilterFactory – filter to use with Morfologik stemmer
69. ¿Questions?
70. Resources • Solr: http://lucene.apache.org/solr/ • Lucene: https://lucene.apache.org/core/
71. Thank You!