Now do I need to write full text index for all the relationship tables also, or just only in the main table?
If you are not only searching the products table, then other related tables like product_categories and product_types, it is indeed necessary to ensure that full-text indexes are created for all relevant columns across these tables.