Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Index and search data with rails

Index and search data with rails

Vincent Pochet

August 14, 2012
Tweet

More Decks by Vincent Pochet

Other Decks in Programming

Transcript

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size slide