MySQL Fulltext vs Like

Background I have a table with max 2000 rows, the user should search up to 6 columns.

I don't know in advance what he's looking for and i want a concatenated search (search1 AND search2 AND...)

Problem In these columns I have the an ID not the plain description (ie i have the i开发者_Go百科d of the town, not its name). So I was thinking about two solutions:

1. Create another table, where i put keywords (1 key/row) and then i search there using LIKE search1% OR LIKE search2% ...
2. Add a field to the existent table where I put all the keywords and then do a FULLTEXT on that

Which one is the best? I know that rows are so fews that there won't be big perfomance problems, but i hope they'll get more and more :)

Example

This is my table:

ID | TOWN | TYPE | ADDRESS |

11| 14132 | 3 | baker street 220

13| 45632 | 8 | main street 12

14132 = London

45632 = New York

3 = Customer

8 = Admin

The user typing "London Customer" should find the first row.

---

If you're simply going to use a series of LIKEs, then I'd have thought it would make sense to make use of a FULLTEXT index, the main reason being that it would let you use more complex boolean queries in the future. (As @Quassnoi states, you can simply create an index if you don't have a use for a specific field.)

However, it should be noted that fulltext has its limitations - words that are common across all rows have a low "score" and hence won't match as prominently as if you'd carried out a series of LIKEs. (On the flipside, you can of course get a "score" back from a FULLTEXT query, which may be of use depending on how you want to rank the results.)

---

You don't have to create a separate field, since a FULLTEXT index can be created on multiple fields:

CREATE  fx_mytable_fields ON mytable (field1, field2, field3)

SELECT  *
FROM    mytable
WHERE   MATCH(field1, field2, field3) AGAINST ('+search1 +search2')

This will return all records that contain search1 and search2 in either of the fields, like this:

field1   field2     field3
--       --         --
search1  something  search2

or this:

field1           field2      field3
--               --          --
search1 search2  something   something else

---

Given you've got the data in seperate tables, you'd have to have a FULLTEXT index on each of the searchable fields in each table. After that, it's just a matter of building the query with the appropriate JOINs in place so you can fulltext MATCH AGAINST the text version of the field, and not the foreign key number.

SELECT user.id, user.name, town.name
FROM user
LEFT JOIN town ON user.town = town.id
WHERE MATCH(user.name, town.name) AGAINST (...)