MySQL query to search keywords ordered by relative match

Question

I am creating an online system that matches bank statements (description, value, type, etc) to purchasers (names, addresses) and would like some feedback on the way I am currently doing this:

$array = array("keywords", "taken", "from", 
               "bank", "statements", "using", "explode", 
               "function", "using", "a", "space");

$i = 0;
$r = array(); //r = relevant

while($i<count($array)) {

  $keyword = $array[$i];

  $get = mysql_query("SELECT `id` FROM `users`, `addresses` 
                      LEFT JOIN `users`.`id` = `addresses`.`user` 
                      WHERE (`users`.`frame` LIKE '%$keyword%' 
                      OR `users`.`lname` LIKE '%$keyword%') 
                      OR ((`addresses`.`address` LIKE '%$keyword%' 
                      OR `addresses`.`town` LIKE '%$keyword%') 
                      OR (`addresses`.`county` LIKE '%$keyword%' 
                      OR `postcode`.`town` LIKE '%$keyword%'));");

  if(mysql_num_rows($get)) {
    while($fetch = mysql_fetch_array($get)) {
      list($var) = $fetch;
      push_array($r, $var);
    }
  }

  $i++;

}

//count the IDs that match within the relative array to see
//which is most relative to the search

Is there a better way of doing this as well as keeping the execute time to an absolute minimum? The database will be in the 10s of thousands when it's finished.

score 1 · Accepted Answer · answered Jul 02 '12 at 14:06

It would be better to build a keyword table that ties each keyword to a user, e.g.:

keywords (keyword, user) + UNIQUE(keyword, user)

keyword1   12
keyword1   14
keyword2   3

After you populate the keywords table from the data you wish to search on, the query becomes much more optimal:

SELECT users.* 
FROM keywords 
INNER JOIN users ON users.id = keywords.user
WHERE keyword IN ('keyword1', 'keyword2')

Of course, you need to maintain this table when you make changes to the user or address table (insert, update, delete).

MySQL query to search keywords ordered by relative match

1 Answers1