Hello I am trying to do a full text search with multiple tables... I don't know if it can be done, but it's really frustrating me, I can get it to work partially, here is my code:
SELECT menuitems.*, categories.categoryName, categories.categoryDescription,((1.3*(MATCH (menuName) AGAINST ('("beef taco")' IN BOOLEAN MODE))) + (0.6*(MATCH (menuDescription,categoryName,categoryDescription) AGAINST ('("beef taco") (+beef +taco)' IN BOOLEAN MODE)))) AS relevance FROM menuitems, categories WHERE (MATCH (menuName,menuDescription,categoryName,categoryDescription) AGAINST ('("beef taco") (+beef +taco)' IN BOOLEAN MODE)) AND menuitems.menuType = categories.categoryID GROUP BY menuitems.menuID ORDER BY relevance ASC LIMIT 0, 20
This works fine... the problem arises when I try to get info from another table called listings which also has full text indexes. I don't care about using the full text indexes from listings, is there a way to JOIN this table to my query?
I have tried this, and then it just takes like 3 minutes to load, which is not acceptable:
SELECT menuitems.*, categories.categoryName, categories.categoryDescription,listings.listinglat,listings.listinglong,((1.3*(MATCH (menuName) AGAINST ('("beef taco")' IN BOOLEAN MODE))) + (0.6*(MATCH (menuDescription,categoryName,categoryDescription) AGAINST ('("beef taco") (+beef +taco)' IN BOOLEAN MODE)))) AS relevance FROM menuitems, categories, listings WHERE (MATCH (menuName,menuDescription,categoryName,categoryDescription) AGAINST ('("beef taco") (+beef +taco)' IN BOOLEAN MODE)) AND menuitems.menuType = categories.categoryID GROUP BY menuitems.menuID ORDER BY relevance ASC LIMIT 0, 20
