Mysql INNER JOIN + MATCH AGAINST: how to speed up

Question

I'm wondering how to speed up the query below. I'm using different INNER JOINs to retrive contents belonging to different nodes categories. Obviously there is a FULLTEXT index ON txt_for_search.

SELECT DISTINCT(c.id_content)) as tot, c.* FROM Contents c 
  INNER JOIN (SELECT id_content FROM Content_Nodes 
    WHERE id_node IN (139)) cn1 ON c.id_content=cn1.id_content 
  INNER JOIN (SELECT id_content FROM Content_Nodes 
    WHERE id_node IN (6)) cn2 ON c.id_content=cn2.id_content 
  INNER JOIN (SELECT id_content FROM Content_Nodes 
    WHERE id_node IN (389,399)) cn3 ON c.id_content=cn3.id_content  
WHERE MATCH(txt_for_search) AGAINST ('my query'  IN BOOLEAN MODE)  and  DATEDIFF(NOW(),to_date)<=365  and enabled=1

If I do this query is super fast

SELECT DISTINCT(c.id_content)) as tot, c.* FROM Contents c  
WHERE MATCH(txt_for_search) AGAINST ('my query'  IN BOOLEAN MODE)  and  DATEDIFF(NOW(),to_date)<=365  and enabled=1

Even the SELECT related with INNER JOINs are super fast.

Together I have to wait more than 100 seconds...

Answer 1

I tried join decomposition and it works. I also changed the SELECT IN with find_in set as suggested here.

I don't know if it's the best. I would prefer INNER JOINs to have all in one query, but I'm not able to find a different solution.