MySQL Full-Text Search Across Multiple Tables - Quick/Long Solution?

Question

Hello all,

I have been doing a bit of research on full-text searches as we realized a series of LIKE statements are terrible. My first find was MySQL full-text searches. I tried to implement this and it worked on one table, failed when I was trying to join multiple tables, and so I consulted stackoverflow's articles (look at the end for a list of the ones I've been to)

I didn't see anything that clearly answered my questions. I'm trying to get this done literally in an hour or two (quick solution) but I also want to do a better long term solution. Here is my query:

SELECT 
    a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, 
    b.`price`, c.`image`, c.`swatch`, e.`name` AS industry 
FROM `products` AS a 
LEFT JOIN `website_products` AS b ON (a.`product_id` = b.`product_id`)
LEFT JOIN 
    ( SELECT `product_id`, `image`, `swatch` FROM `product_images` 
      WHERE `sequence` = 0) AS c ON (a.`product_id` = c.`product_id`) 
LEFT JOIN `brands` AS d ON (a.`brand_id` = d.`brand_id`) 
INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`) 
WHERE 
    b.`website_id` = 96 
    AND b.`status` = 1 
    AND b.`active` = 1 
    AND MATCH( a.`name`, a.`sku`, a.`description`, d.`name` ) AGAINST ( 'ashley sofa' ) 
GROUP BY a.`product_id` 
ORDER BY b.`sequence` LIMIT 0, 9

The error I get is: Incorrect arguments to MATCH

If I remove d.name from the MATCH statement it works. I have a full-text index on that column.

I saw one of the articles say to use an OR MATCH for this table, but won't that lose the effectiveness of being able to rank them together or match them properly?

Other places said to use UNIONs but I don't know how to do that properly.

Any advice would be greatly appreciated.

---

In the idea of a long term solution it seems that either Sphinx or Lucene is best. Now by no means and I a MySQL guru, and I heard that Lucene is a bit more complicated to setup, any recommendations or directions would be great.

Articles:

http://stackoverflow.com/questions/1117005/mysql-full-text-search-across-multiple-tables http://stackoverflow.com/questions/668371/mysql-fulltext-search-across-1-table http://stackoverflow.com/questions/2378366/mysql-how-to-make-multiple-table-fulltext-search http://stackoverflow.com/questions/737275/pros-cons-of-full-text-search-engine-lucene-sphinx-postgresql-full-text-searc http://stackoverflow.com/questions/1059253/searching-across-multiple-tables-best-practices

Answer 1

Hi Kerry. For the short-term solution, I suggest creating a table of just the full-text values, as in this question.

For the long-term solution, please take a look at Solr. It is much easier to install than Lucene, and yet gives you most of its functionality. I have also heard good things about Sphinx, but have not personally ever used it.