How to create a index to search firstname or last name (Active Admin in rails)

userIT3D 0
Viewed 38

I have search functionality in active admin of rails which has filter option:

  filter :profile_display_name_or_profile_firstname_or_profile_lastname_or_email,
         as: :string,
         label: "Search Name/Email"

which generates below query

"SELECT COUNT ( * ) FROM accounts LEFT OUTER JOIN profiles ON profiles . account_id = 
accounts . id WHERE ( ( ( profiles . display_name LIKE ? OR profiles . firstname LIKE ? )
 OR profiles . lastname LIKE ? ) OR accounts . email LIKE ? )"

and this is making slower performance in production. I want to speed the performance time.

so I am trying to separate the filters

  filter :profile_display_name_or_profile_firstname_or_profile_lastname,
         as: :string,
         filters: [:starts_with, :equals, :contains, :ends_with],
         label: "Search Name"
  filter :email,
         as: :string,
         filters: [:starts_with, :equals, :contains, :ends_with],
         label: "Search Email"

Also, I want to create the index on first_name, last_name , display_name

add_index :profiles, %i[firstname lastname displayname],
              name: "index_profiles_on_firstname_and_lastname_and_displayname"

My question is I am searching with either first name or lastname or displayname do I need to index all once or separately.

what should be good solution to optimise the above query.Thanks

activeadmin ruby ruby-on-rails mysql indexing
1 Answers

Add this index

FULLTEXT(display_name, firstname, lastname, lastname, email)

Then do

WHERE MATCH(display_name, firstname, lastname, lastname, email)
      AGAINST('?' IN BOOLEAN MODE)
Rick James0
Related