php - MySQL 查询按相对匹配顺序搜索关键字

Question

我正在创建一个在线系统，将银行对账单(描述、值(value)、类型等)与购买者(姓名、地址)相匹配，并希望获得一些关于我目前这样做的方式的反馈:

$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

是否有更好的方法来做到这一点并将执行时间保持在绝对最低限度？完成后数据库将达到数十万。

Answer 1

最好建立一个关键字表，将每个关键字与用户联系起来，例如:

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

keyword1   12
keyword1   14
keyword2   3

从您想要搜索的数据填充关键字表后，查询变得更加优化:

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

当然，当您对用户或地址表进行更改(插入、更新、删除)时，您需要维护此表。