任何方式在InnoDB上实现全文搜索 [英] Any way to achieve fulltext-like search on InnoDB

问题描述

我有一个非常简单的查询：

pre $ SELECT ... WHERE行LIKE'％some％'OR row LIKE' ％search％'OR row LIKE'％string％'


搜索一些搜索字符串，但正如您所看到的，它会单独搜索每个字符串，并且对性能也不是很好。

有没有办法在InnoDB表上使用LIKE重新创建一个类似全文的搜索。当然，我知道我可以使用类似Sphinx的东西来实现这一点，但我正在寻找一个纯粹的MySQL解决方案。

例如，使用myisam全文表格索引回你的innodb表格：

使用innodb构建您的系统：

 创建表用户（...）engine = innodb; 
 
创建表论坛（...）engine = innodb; 
 
创建表线程
（
 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， - 要查找这个... !! 
 created_date datetime not null，
 next_reply_id int unsigned not null default 0，
 view_count int unsigned not null默认0，
主键（forum_id，thread_id） - 复合聚集PK索引
）
 engine = innodb; 
  

现在我们将使用全文搜索表来索引我们的innodb表。您可以通过使用触发器或每晚批量更新等来维护此表中的行。

  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）， - 主题$全文索引b $ b主键（forum_id，thread_id） - 复合非聚集索引
）
 engine = myisam; 
  

最后是您从php / application调用的搜索存储过程：

  drop procedure if exists ft_search_threads; 
 delimiter＃
 
 create procedure ft_search_threads 
（
 in p_search varchar（255）
）
 begin 
 
选择
 t。*，
 f.title作为forum_title，
 u.username，
 match（tft.subject）against（p_search in boolean mode）as 
 from 
 threads_ft tft 
内部连接线程t on tft.forum_id = t.forum_id和tft.thread_id = t.thread_id 
内部连接论坛f on t.forum_id = f.forum_id 
内部联接用户u上t.user_id = u.user_id 
其中
匹配（tft.subject）针对（p_search，布尔模式）
命令
排名desc 
限制100; 
 
 end; 
 
调用ft_search_threads（'+ innodb + clustered + index'）; 
  

希望这有助于：）

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.

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.

解决方案

use a myisam fulltext table to index back into your innodb tables for example:

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;

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;

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');

Hope this helps :)

这篇关于任何方式在InnoDB上实现全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！