7

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)
ggez44
  • 895
  • 8
  • 17

1 Answers1

0

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.

Sebas
  • 21,192
  • 9
  • 55
  • 109
  • Thanks @Sebas. so an index merge would only be needed in the way you have your suggestion or the initial way i wrote the query. however, with a selfjoin, theoretically the author_id index would just be used to join matching rows in the 2 tables.. then hopefully the fulltext would work on the resulting filtered set? – ggez44 Jun 27 '12 at 01:03
  • According to what says the mysql doc it will only use one index per table reference. The where clause is considered belonging to a table reference, so we can't really talk about a `resulting filtered set`. The where clause is evaluated before we get an actual `resulting filtered set`... I'd be curious to see the explain plan though. – Sebas Jun 27 '12 at 01:07
  • added the explain plan.. also yeah i was talking about `resulting filtered set` at the database level.. just depends on the order mysql tries to do the lookup.. the plan that it tried to explain if you will =) – ggez44 Jun 27 '12 at 17:54
  • what is the volume of your tables you used for the test? – Sebas Jun 27 '12 at 18:39