Any way to help full-text search with a different index?

Question

Let's say I have an article table that has columns in it:

article_text: fulltext indexed author_id: indexed 

Now I want to find a term that appears in an article written by a particular arthor.

so something like:

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

An explanation of this query tells me that mysql will only use the full text. I suppose mysql can only use one index, but it seems like a wise idea to get all the articles the author wrote first, before the full-text search of this term ... anyway to help mysql?

for example .. if you made an independent 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 explanation for this lists first using the author_id index, and then a full-text search.

Does this mean that in fact it only performs full-text search on a limited set that is filtered by author_id?

ADDITION

explain the self-join plan 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) 

Answer 1

So, since

Index merging does not apply 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 with your index name with author_id)

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

Here the problem is this:

• it’s really impossible to use a regular index in combination with a full-text index
• If you join the table with yourself, you are already using the index on each side of the join (the ON clause will use the author_id column, you definitely need an index here)

The most effective should be chosen by you with some examples of tests, if using the author’s index is better than textual.