Databases
StackOverflow2013
Postsdbo
POBest way to optimize query while searching string in big sentences
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to optimize query while searching string in big sentences
    primarykey
    data
    text
    <p>I have requirement as below</p> <blockquote> <p>poem belongs to poet</p> <p>poet has many poems</p> </blockquote> <p>If user searching for word "ruby"</p> <p>It should give, </p> <blockquote> <p>Total number of times word ruby used in all poems.</p> <p>Show all the poems which has the word ruby. </p> <p>Number of times word ruby used in each poems.</p> <p>Total number of poets used the word ruby.</p> <p>Total number of times each poets used the word ruby.</p> </blockquote> <p>So my query in model Poem is here</p> <pre><code> 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 </code></pre> <p>And to get poets count in Poem Model </p> <pre><code> @poets = poems.select("distinct(poet_id)") @poets.each do |poet| @poets_word_count &lt;&lt; poems.where("poet_id = #{poem.poet_id}").count end </code></pre> <p>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.</p> <p>i think the below lines taking too much time as it looping each words of all poems.</p> <pre><code> words.each do |word| count += 1 if word.upcase.include?(word.upcase) end </code></pre> <p>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.</p> <p>Thanks in advance</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USdevudilip
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest way to optimize query while searching string in big sentences
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBest way to optimize query while searching string in big sentences
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POBest way to optimize query while searching string in big sentences
      1. This table or related slice is empty.
      1. VTUpMod
    1. COdo you know where is taking the most time: getting poets? getting number of occurrences for the searched word inside poem_column?
      singulars
      1. POBest way to optimize query while searching string in big sentences
      1. USjuanpastas
    2. COBoth taking time but searched word inside poem_column taking more.[poem column contains up to 20 lines]
      singulars
      1. POBest way to optimize query while searching string in big sentences
      1. USdevudilip
    3. COConsider using [SQLite3 Full Text Search](http://www.sqlite.org/fts3.html).
      singulars
      1. POBest way to optimize query while searching string in big sentences
      1. USLS_ᴅᴇᴠ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload