joins and full-text search

[mPeror]

Hi,

I'm basically trying to perform a join on multiple tables, search the records (after the join), and sort results according to relevance.

I'm not sure how to do this since I calculate the relevance in the beginning of the SELECT statement like this:

SELECT table_name.*, MATCH(table_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance

Any idea how I can overcome this issue?

[r937]

what exactly is the issue? i don't see any join there...

[mPeror]

Sorry for not explaining it better.

The issue is that I don't know how to include fields from the join in the search. If I do this for example:

SELECT table_name.*, MATCH(table_name.field, table2_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance
 
FROM table_name
 
LEFT JOIN table2_name ON table2_name.id = table_name.foreign_key

I get an "unknown column" error.

[longneck]

you can't do that. MATCH() requires all columns to be from the same table.

try this:

SELECT table_name.*
     , MATCH(table_name.FIELD) AGAINST('something' IN BOOLEAN MODE) * MATCH(table2_name.FIELD) AGAINST('something' IN BOOLEAN MODE) AS relevance
  FROM table_name
LEFT OUTER
  JOIN table2_name
    ON table2_name.id = table_name.foreign_key

[mPeror]

Thanks longneck.

By the way, is that the best way to search MySQL records? I keep duplicating tables (since I use InnoDB) just to use full-text search, and it's getting kind of tiresome.

[r937]

I keep duplicating tables (since I use InnoDB) just to use full-text search, and it's getting kind of tiresome.

perhaps be more judicious about when to use fulltext search and when to use plain old LIKE