任何在 InnoDB 上实现类全文搜索的方法
我有一个非常简单的查询:
I have a very simple query:
SELECT ... WHERE row LIKE '%some%' OR row LIKE '%search%' OR row LIKE '%string%'
搜索一些搜索字符串
,但是如你所见,它单独搜索每个字符串,这对性能也不利.
to search for some search string
, but as you can see, it searches for each string individually and it's also not good for performance.
有没有办法在 InnoDB 表上使用 LIKE 重新创建类似全文的搜索.当然,我知道我可以使用 Sphinx 之类的东西来实现这一点,但我正在寻找纯 MySQL 解决方案.
Is there a way to recreate a fulltext-like search using LIKE on an InnoDB table. Of course, I know I can use something like Sphinx to achieve this but I'm looking for a pure MySQL solution.
推荐答案
使用 myisam 全文表索引回你的 innodb 表,例如:
use a myisam fulltext table to index back into your innodb tables for example:
使用 innodb 构建您的系统:
Build your system using innodb:
create table users (...) engine=innodb;
create table forums (...) engine=innodb;
create table threads
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
user_id int unsigned not null,
subject varchar(255) not null, -- gonna want to search this... !!
created_date datetime not null,
next_reply_id int unsigned not null default 0,
view_count int unsigned not null default 0,
primary key (forum_id, thread_id) -- composite clustered PK index
)
engine=innodb;
现在是全文搜索表,我们将使用它来索引回我们的 innodb 表.您可以使用触发器或每晚批量更新等方式维护该表中的行.
Now the fulltext search table which we will use just to index back into our innodb tables. You can maintain rows in this table either by using a trigger or nightly batch updates etc.
create table threads_ft
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
subject varchar(255) not null,
fulltext (subject), -- fulltext index on subject
primary key (forum_id, thread_id) -- composite non-clustered index
)
engine=myisam;
最后是您从 php/应用程序调用的搜索存储过程:
Finally the search stored procedure which you call from your php/application:
drop procedure if exists ft_search_threads;
delimiter #
create procedure ft_search_threads
(
in p_search varchar(255)
)
begin
select
t.*,
f.title as forum_title,
u.username,
match(tft.subject) against (p_search in boolean mode) as rank
from
threads_ft tft
inner join threads t on tft.forum_id = t.forum_id and tft.thread_id = t.thread_id
inner join forums f on t.forum_id = f.forum_id
inner join users u on t.user_id = u.user_id
where
match(tft.subject) against (p_search in boolean mode)
order by
rank desc
limit 100;
end;
call ft_search_threads('+innodb +clustered +index');
希望这有帮助:)
相关文章