Best way to optimize query while searching string in big sentences

Question

I have requirement as below

poem belongs to poet

poet has many poems

If user searching for word "ruby"

It should give,

Total number of times word ruby used in all poems.

Show all the poems which has the word ruby.

Number of times word ruby used in each poems.

Total number of poets used the word ruby.

Total number of times each poets used the word ruby.

So my query in model Poem is here

    poems= where("poem_column like ?", "%#{word}%" )
    @results = {}
    poems.each do |poem|
      words = poem.poem_column.split
      count = 0
      words.each do |word|
        count += 1 if word.upcase.include?(word.upcase)
      end
      @results[poem] = count # to get each poem using word ruby
    end

And to get poets count in Poem Model

   @poets = poems.select("distinct(poet_id)")
      @poets.each do |poet|
        @poets_word_count << poems.where("poet_id = #{poem.poet_id}").count
      end

Where poems are around 50k. its taking almost more than 1 minute. I know am doing in wrong way but i couldnt optimize it in any other way.

i think the below lines taking too much time as it looping each words of all poems.

      words.each do |word|
        count += 1 if word.upcase.include?(word.upcase)
      end

Can anyone of you show me the way to optimize it.As lack of knowledge in queries i couldnt do it in any other way.

Thanks in advance

Answer 1

Not an answer, just a test.

First, reduce the data extracting keywords for each poem as they are saved:

rails g resource Keyword word occurrences poem_id:integer
rails db:migrate

Then in your Poem model:

# add more words
EXCLUDED_WORDS = %w( the a an so that this these those )

has_many :keywords

before_save :set_keywords

# { :some => 3, :word => 2, :another => 1}
def keywords_hash(how_many = 5)
  words = Hash.new 0
  poem_column.split.each do |word|
    words[word] += 1 if not word.in? EXCLUDED_WORDS
  end
  Hash[words.sort { |w, w1| w1 <=> w }.take(how_many)]
end

def set_keywords
  keywords_hash.each do | word, occurrences |
    keywords.create :word => word, :occurrences => occurrences
  end
end

In Keyword model:

belongs_to :poem

def self.poem_ids
  includes(:poem).map(&:poem_id)
end

def self.poems
  Poem.where(id: poem_ids)
end

Then when you have word to search for:

keywords = Keyword.where(word: word)
poems = keywords.poems
poets = poems.poets

To use this last part, you would need in Poem model:

def self.poet_ids
  includes(:poet).map(&:poet_id)
end

def self.poets
  Poet.where(id: poet_ids)
end

As far as I see this way would require just 3 queries, no joins, so it seems to make sense.

I will think in how to extend this way to search by the entire content.