Edit Question - KnowledgeHub

Title Body

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)

Tags (comma-separated) Cancel