Full-text search on two tables

[zippee]

Hi, I managed to use the full-text search on a single table (product) and would like to search two tables (product and article) simultaneously, when user enter a keyword. Can anyone help?

Thanks

[cscgal]

Have been wondering the same. I believe you can only do a full text search on selected fields within a single table. In the past when I was in dire need of this feature and didn't have time to experiment, I did a brute force method where I ran a full text search on one table, a second full text search on a second table, and then merged the resultsets.

Of course, you can do a full text search on the article table and left join in the product table (without searching it).

[zippee]

em... interesting. I was thinking of storing results from both tables (separate full-text search as you mentioned) in array, then sort them based on their scores, and display them. Not sure this may cause a long delay... any thought?

[cscgal]

Yes, that's what I've done in the past. It did put a heavy load on the server.

[mr.adrian]

i hve a problem in searching a two tables..?

[jsalis]

Sorry to revive such an old thread, but I rather hoped that since newer versions of MySQL are now in wide circulation an answer will be available.
I tried the following:

SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (p.products_model) AGAINST ('+Speaker +Mic Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1

then I tried:

SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (pd.products_name,pd.products_description) AGAINST ('"Speaker Mic" >Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1

I get a total of seven results returned from these two queries. However, when I tried the following I got a little over 62K results.

SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (p.products_model) AGAINST ('+Speaker +Mic Miner' IN BOOLEAN MODE)
       OR MATCH (pd.products_name,pd.products_description) AGAINST ('"Speaker Mic" >Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1

Does anyone have any idea how to accomplish this?

[jsalis]

I found the solution to this. I was apparently very close. The MATCH AGAINST OR MATCH AGAINST was the way to go, but I was not joining the tables correctly. Here is again with LEFT JOIN instead and relevancy scoring.

SELECT pd.products_id, pd.products_name, p.products_price, MATCH (p.products_model) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      OR MATCH (pd.products_name,pd.products_description) AGAINST ('LAA 0209' IN BOOLEAN MODE) AS score
      FROM products_description pd
		LEFT JOIN products p
		ON pd.products_id = p.products_id 
      WHERE MATCH (p.products_model) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      OR MATCH (pd.products_name,pd.products_description) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      AND p.products_status = 1
      ORDER BY score DESC