mysql search query

I have a database table that stores imported information. For simplicity, its someth开发者_JAVA百科ing like:

CREATE TABLE `data_import` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`payee` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `payee` (`payee`)
)

I also have a table that stores import rules:

CREATE TABLE `import_rules` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`search` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `search` (`search`)
)

The idea is that for each imported transaction, the query needs to try find a single matching rule - this match is done on the data_import.payee and import_rules.seach fields. Because these are both varchar fields, I have indexed them in the hope of making the query faster.

This is what I have come up with so far, which seems to work fine.

SELECT i.id, i.payee, i.amount, i.posted r.id, r.search
FROM import_data id
LEFT JOIN import_rules ir on i.payee = ir.search

So, for example, lets say that we have a import_rules.search record of 'coca cola', and a import_data.payee record of 'coca cola'. Then we get a match.

Now, lets say that we want to make this a bit more flexible, and have it so that even if the search matches part of the payee, then we get a match. So, import_rules.search = 'cola' and import_data.payee = 'coca cola' should still result in a match.

My understanding is that the various "LIKE '%search%'" has bad performance. I am running on innodb, so not sure if the text search alternatives from myisam are an option. I may be able to switch that one table if needed.

for that case you can use MATCH (field1) AGAINST(value) for searching the perticular words from the database.