Index and search data with rails

Transcript

1. Index and search data
   with rails
   Vincent Pochet
   @vin100pochet
   

   View Slide

2. Search in database
   ● Basic matching
   ● Basic full text search
   ● Advanced full text search
   ● Gems
   ● Resources
   

   View Slide

3. Search in database
   Basic matching
   ● +
   ○ Simple
   ● -
   ○ Exact matching only
   ○ Performance
   ○ No relevance ranking
   Track.where('name ILIKE ? OR artist ILIKE ?', "%#{The Doors}%")
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   ○ ...
   

   View Slide

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
   

   View Slide

8. Search with a search engine
   ● Sphinx
   ● Thinking Sphinx
   ● Resources
   

   View Slide

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)
   

   View Slide

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
    

    View Slide

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'
    

    View Slide

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
    

    View Slide

13. Questions
    ?
    

    View Slide