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
