CODEWITHSUNDEEP
mysqlfull-text-searchsql-like
tampe125
tampe125

Reputation: 8543

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 id 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.

Upvotes: 0

Views: 2529

Answers (3)

John Parker
John Parker

Reputation: 54445

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.)

Upvotes: 1

Marc B
Marc B

Reputation: 360842

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 (...)

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425753

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

Upvotes: 0

Related Questions