如何使用ORDER BY快速进行全文搜索? [英] How to make a FULLTEXT search with ORDER BY fast?

问题描述

我正在尝试获得简单的FULLTEXT匹配，以在具有超过1亿行的表的另一列上使用order by时更快.是否可以快速在另一个索引列上按顺序生成FULLTEXT?下面的SQL Fiddle具有模式，并解释了所有查询:

I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. Is it possible to make a FULLTEXT with an order by on another indexed column fast? SQL Fiddle below with schema and explains of all queries:

http://sqlfiddle.com/#!9/ed646c/1

到目前为止，我拥有的是非规范化和联接，但这需要一个单独的表，如果不需要，我宁愿没有另一个表.下面的SQL Fiddle(末尾为非规范化查询):

What I have so far is denormalization and a join but this requires a separate table and I would rather not have another table if not necessary. SQL Fiddle below (denormalized query at the end):

http://sqlfiddle.com/#!9/6aabf/8

我愿意进行模式更改，或者如果需要的话，还可以使用完整的搜索引擎.

I'm open to schema changes or, if I have to, a full search engine on top.

推荐答案

您不需要相同数据的两个副本；您可以针对非正规化"案例进行自我加入".

You don't need two copies of the same data; you can "self join" for the "denormalized" case.

这篇关于如何使用ORDER BY快速进行全文搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！