mysql fulltext search as level2 index

Question

lets say I have a table with product details. one of the fields is category (integer).

I want to do fulltext search on product name in specific category.

Unfortunately Mysql does not allow me to specify index that includes category and product name as fulltext. It looks like I can use fulltext only on product_name and theefore any fulltext search will search product names in all categories.

Is there any way to solve this (allow fulltext search/index only on products in category)?

Answer 1

No this is not possible, full-text-search in MyIsam will everytime scan the whole full-text-index but by adding a WHERE-condition to your SQL-query you can filter out results which are not in the category you wanted to search.

Answer 2

From a functional perspective you can do the fulltext search on product name and also filter by category in the where clause.

select id,product_name
from product_detail
WHERE category = 17  
AND match(product_name) against('+foo*' IN BOOLEAN MODE); 

If you are trying to optimize performance, you can create a separate fulltext index for product names in each category by using separate columns or tables. For example you could have column/table names product_name_cat1, product_name_cat2, etc. Then you would need to dynamically build the search query to use the appropriate column/table based on the category. This would give you somewhat better performance because each fulltext index would be smaller than a combined index that contained all product names for all categories.

select id,product_name
from product_detail
WHERE category = 17  
AND match(product_name_cat17) against('+foo*' IN BOOLEAN MODE);