MySQL的 - 任何方式来帮助全文搜索与另一个索引？ [英] mysql - any way to help fulltext search with another index?

问题描述

假设我有一个articles表，它包含以下列：

  article_text：全文索引
 author_id ：indexed 
  

现在我想要搜索一篇出现在特定arthor撰写的文章中的术语。

所以类似：

  select * from articles 
 where author_id = 54 
和match（article_text）against（'foo'）; 
  

这个查询的解释告诉我mysql只打算使用全文索引。
我相信mysql只能使用1个索引，但它肯定似乎是一个明智的想法，让特定作者在全文搜索该术语之前先写入的所有文章......那么无论如何帮助mysql？ / p>

举个例子..如果你做了自我加入？

 从文章中选择文章作为acopy 
加入关于acopy.author_id = articles.author_id 
的文章，其中
 articles.author_id = 54 
和match（article_text）against（'foo' ）; 
  

这个解释列出了先使用author_id索引，然后是全文搜索。

这是否意味着它实际上只对author_id过滤的有限集合进行全文搜索？

附录

解释自连接的计划如下：

  *************************** 1. row ****************** ********* 
 id：1 
 select_type：SIMPLE 
表：acopy 
类型：ref 
 possible_keys：index_articles_on_author_id 
 key： index_articles_on_author_id 
 key_len：5 
 ref：const 
 rows：20 
 filtered：100.00 
 Extra：Using where;使用索引
 *************************** 2. row ************** ************* 
 id：1 
 select_type：SIMPLE 
 table：articles 
 type：fulltext 
 possible_keys：index_articles_on_author_id，fulltext_articles 
 key：fulltext_articles 
 key_len：0 
 ref：
 rows：1 
过滤：100.00 
额外：使用其中
 2行（0.00 sec）
  

解决方案

p>

索引合并不适用于全文索引

http://dev.mysql.com/ doc / refman / 5.0 / en / index-merge-optimization.html

我会尝试这种方法：（替换 author_id_index 按您在author_id上的索引名称）

  select * from articles use ind ex（author_id_index）
 where author_id = 54 
和match（article_text）against（'foo'）; 
  

问题如下：

• 如果您将表与自身结合使用，那么将常规索引与全文索引结合使用确实是不可能的，您已经使用了索引在联接的每一边（ON子句将使用author_id列，您在这里需要索引）

效率最高的由你决定，在一些测试用例中，使用作者索引是否优于文本索引。

Let's say i have an "articles" table which has the columns:

article_text:  fulltext indexed
author_id:     indexed

now i want to search for a term that appears in an article that a particular arthor has written.

so something like:

select * from articles 
where author_id=54 
and match (article_text) against ('foo');

the explain for this query tells me that mysql is only going to use the fulltext index. I believe mysql can only use 1 index, but it sure seems like a wise idea to get all the articles a particular author has written first before fulltext searching for the term... so is there anyway to help mysql?

for example.. if you did a self-join?

select articles.* from articles as acopy 
                  join articles on acopy.author_id = articles.author_id 
where 
    articles.author_id = 54 
and match(article_text) against ('foo');

the explain for this lists the use of the author_id index first, then the fulltext search.

does that mean it's actually only doing the fulltext search on the limited set as filtered by author_id?

ADDENDUM

explain plan for the self join as follows:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: acopy
         type: ref
possible_keys: index_articles_on_author_id
          key: index_articles_on_author_id
      key_len: 5
          ref: const
         rows: 20
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
         type: fulltext
possible_keys: index_articles_on_author_id,fulltext_articles
          key: fulltext_articles
      key_len: 0
          ref: 
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set (0.00 sec)

解决方案

Ok, so, since

Index Merge is not applicable to full-text indexes

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

I would try this approach: (replace author_id_index by the name of your index on author_id)

select * from articles use index (author_id_index)
where author_id=54 
and match (article_text) against ('foo');

Here the problem is the following:

• it is indeed impossible to use a regular index in combination with a full-text index
• if you join the table with itself, you are using an index already on each side of the join (the ON clause will use the author_id column, you definetly need the index here)

The most efficient has to be decided by you, with some test cases, whether using the author index is better than the text one.

这篇关于MySQL的 - 任何方式来帮助全文搜索与另一个索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！