Advice on advanced searching for a MySQL store catalog requested

[fpearl]

Hi there.  I am trying to develop an all purpose MySQL default query for my web store. I want results to come from the product name, product description, and product merchant (its a mall-type store). The product_name and product_description are part of a FULLTEXT index, whereas the merchant name is not.

 

The "?" in the sql statement below are placeholders for prepared input (i.e. for mysqli prepared statements). I'd like the results to be sorted by relevance, where products with a matching name or matching merchant are most relevant and products with matching descriptions are the second most relevant.

 

This is what I have now:

SELECT * FROM products p LEFT JOIN merchants a ON p.product_merchant_id = a.merchant_id WHERE (MATCH(product_name,product_description) AGAINST (?) OR merchant_name LIKE ?) AND p.active = 1

 

It works fair, but perhaps someone with more experience can give additional advice for how this should be done.

 

Anna

[The Little Guy]

This snippet has an example of what you are looking for:

http://phpsnips.com/snip-94

 

 

SELECT SQL_CALC_FOUND_ROWS *, 
MATCH(column1) AGAINST ('Im searching for something important' IN BOOLEAN mode) AS score1, 
MATCH(column2) AGAINST ('Im searching for something important' IN BOOLEAN mode) AS score2 
FROM my_database
WHERE   
MATCH(column1,column2) AGAINST ('Im searching for something important' IN BOOLEAN mode)  
ORDER BY score1 DESC, score2 DESC LIMIT 0, 10;

[fenway]

SQL_CALC_FOUND_ROWS can be insanely slow.

[fpearl]

I am using MySQL version 5.0.92 I believe.

[fenway]

I am using MySQL version 5.0.92 I believe.

You'd have to benchmark it-- a count is usually faster.