MySQL search query

[Jonob]

I have a database table that stores imported information. For simplicity, its something 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. There won't always be a match, and thats fine. 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 id.id, id.payee, ir.id, ir.search
FROM import_data id
LEFT JOIN import_rules ir on i.payee = ir.search

 

I would like to now make this search functionality a little bit more powerful, so that a partial match can be returned. Lets say, for example,

that we have id.payee = 'coca cola'  and ir.search = 'cola'. This should still return a match.

 

My understanding is that "LIKE '%search%'" has bad performance.

 

Are there any other alternatives?

[fenway]

This is a full-text search request -- not great for joins, and not great in general.