title: mysql search query
body: <p>I have a database table that stores imported information. For simplicity, its something like:</p>
  
  <pre><code>CREATE TABLE `data_import` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `payee` VARCHAR(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `payee` (`payee`)
  )
  </code></pre>
  
  <p>I also have a table that stores import rules:</p>
  
  <pre><code>CREATE TABLE `import_rules` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `search` VARCHAR(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `search` (`search`)
  )
  </code></pre>
  
  <p>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.</p>
  
  <p>This is what I have come up with so far, which seems to work fine. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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. </p>