如何创建索引来搜索名字或姓氏(rails中的Active Admin)

Question

我在rails的活动管理中有搜索功能，它有过滤选项：

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

其生成低于查询的

"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 ? )"

这使得生产性能下降。我想加快表演时间。

所以我正在尝试分离过滤器

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"

此外，我想在first_name、last_name、display_name 上创建索引

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

我的问题是，我正在用名字、姓氏或显示名搜索，我需要一次索引还是单独索引。

什么应该是优化上述查询的好解决方案。感谢

Answer 1

添加此索引

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

然后进行

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