Index and search data with rails

Transcript

1. Index and search data with rails Vincent Pochet @vin100pochet
2. Search in database • Basic matching • Basic full text search • Advanced full text search • Gems • Resources
3. Search in database Basic matching • + ◦ Simple • - ◦ Exact matching only ◦ Performance ◦ No relevance ranking Track.where('name ILIKE ? OR artist ILIKE ?', "%#{The Doors}%")
4. Search in database Basic full text search Track.where('name @@ ? OR artist @@ ?', 'The Doors', 'The Doors') • + ◦ Simple ◦ Stemming (english by default) ◦ Search for each word • - ◦ No relevance ranking
5. Search in database Advanced full text search • + ◦ Simple ◦ Stemming (english by default) ◦ Logic operator (name @@ !the & doors & !morisson) ◦ Relevance ranking • - ◦ Performance on large fields (even with GIN index) Track.where('name @@ ? OR artist @@ ?', 'The Doors', 'The Doors'). order <<-SQL ts_rank(to_tsvector(name), plainto_tsquery('The Doors')) + ts_rank(to_tsvector(artist), plainto_tsquery('The Doors')) SQL
6. Search in database Gems • Texticle ◦ As simple as a Track.search('The Doors') • PG Search ◦ Advanced params ◦ Search on association ◦ Weight for attributes in relevance ranking ◦ ...
7. Search in database Resources Full text search in PostgreSQL http://www.postgresql.org/docs/9.1/static/textsearch.htm Railscast #343 - Full-Text Search in PostgreSQL (Pro) http://railscasts.com/episodes/343-full-text-search-in-postgresql Texticle http://tenderlove.github.com/texticle/ PG search https://github.com/casecommons/pg_search
8. Search with a search engine • Sphinx • Thinking Sphinx • Resources
9. Search with a search engine Sphinx • + ◦ Powerful matching and ranking mode ◦ Performance ◦ Partial indexation (delta) ◦ Multiple indexes • - ◦ A new dependency for your application ◦ Not real time (yet)
10. Search with a search engine Thinking Sphinx • Define indexes in models based on attributes class Track < ActiveRecord::Base belongs_to :artist define_index do indexes :title indexes artist(:name), as: :artist_name has :artist_id, type: :integer has "privacy = #{Privacies::PUBLIC}", as: :is_public, type: :boolean where "deleted_at IS NULL" set_property delta: :datetime, threshold: 12.minutes end end
11. • Search • Scopes • Built-in pagination and ordering And more super useful stuffs! Search with a search engine Thinking Sphinx Track.search 'All Along the Watchtower' Track.search 'Jimi Hendrix' Track.search 'Little Wing', conditions: {artist: 'Jimi Hendrix'} Track.search 'Voodoo Chile', with: {is_public: true} sphinx_scope(:public) do { with: { is_public: true } } end Track.public.search 'Voodoo Chile'
12. Search with a search engine Resources Sphinx search engine http://sphinxsearch.com/ ThinkingSphinx and Riddle http://freelancing-god.github.com/ts/en/ Railscast #120 - Thinking Sphinx (pro) http://railscasts.com/episodes/120-thinking-sphinx-revised
13. Questions ?