mardi 24 janvier 2023

Ruby on Rails performance of search engine with indexed column

Hi i'm try to check search engine's performance of my ROR application. I have 4 search input forms : title, content, created_on (date) and updated_on (date) I want to check performace of search depending on the presence or absence of an index. (in my case, index presence on created_on and absence on updated_on)

My controller of Post

  def index
    search_start_time = Time.now
    @posts = Post.search(params[:title], params[:content], params[:created_on], params[:updated_on])

    # this line for check performance of search
    puts Time.now - search_start_time
  end

My schema

  create_table 'posts', force: :cascade do |t|
    t.string 'title', null: false
    t.string 'content', null: false
    t.date 'created_on', null: false, index: true
    t.date 'updated_on', null: false
  end

In my post.rb, i maked search method like this

  def self.search(title, content, started_on, finished_on)
    where([
      "title LIKE ? AND content LIKE ? AND CAST(started_on AS text) LIKE ? AND CAST(finished_on AS text) LIKE ?",
      "%#{title}%", "%#{content}%", "%#{started_on}%", "%#{finished_on}%"
    ])
  end

With my code, i performance but there were not big difference with search performance of "indexed" and "not indexed" columns.

Is there a problem with my code? Or does the index not affect the search results? The number of records is 10 million, and an indexed column always comes out similar to the speed of an unindexed column.

I tried to change my search method like this ->

  def self.search(title = '', content = '', started_on = '', finished_on = '')

But there was not difference.

Aucun commentaire:

Enregistrer un commentaire