php - 如何改进全文搜索的 mysql 查询？

Question

我使用以下查询来搜索我的表。但速度很慢，如何才能加快或改善呢？

    if ($stit0 || $sabst0 || $saut0 || $saffl0){
      $qt = "SELECT * FROM mytable WHERE (";
      if ($stit0){ $qt.="(MATCH(title) AGAINST('$stit0' IN BOOLEAN MODE))"; }
      if ($saut0){ $qt.=" AND (MATCH(authors) AGAINST('$saut0' IN BOOLEAN MODE)) "; }
      if ($sabst0){ $qt.=" AND (MATCH(abstract) AGAINST('$sabst0' IN BOOLEAN MODE))"; }
      if ($saffl0){ $qt.=" AND (MATCH(affiliation) AGAINST('$saffl0' IN BOOLEAN MODE))"; }
      if ($_GET[filter]){ $qt.=" AND (pubtype='$_GET[filter]')"; }
      $qt.=") ORDER BY year + pubdate DESC";
      $qt = str_replace("WHERE ( AND", "WHERE (", $qt);
      $qt1 = mysql_query($qt);
      $nqs = mysql_num_rows($qt1);
    }`

Answer 1

您声明了全文索引吗？查询看起来很简单，因此未索引的搜索可能会破坏您的数据库。

对于查询本身，我更喜欢以这种方式形成WHERE子句，这样可以省去很多麻烦。而且查询更具可读性。

// Define a list of constraints, put a default constraint on record types or whatever.
$conditions = array("someColumn = 1");
// Start collecting constraints based on already-sanitized user input 
if ($someFilter) $conditions[] = "someInt = {$someFilter}";
if ($otherFilter) $conditions[] = "MATCH(textColumn) AGAINST('{$otherFilter}')";
// repeat as much as you like, the only drawback is you can't write complex logic here.
// now merge the constraints to form the where-clause
$conditions = implode(' AND ', $conditions);

// Query in heredoc is much cleaner and structured.
$query = <<<HEREDOC

SELECT * FROM yourTable
WHERE {$conditions} 
ORDER BY year DESC, pubDate DESC

HEREDOC;
while ($result = mysql_fetch_assoc($query)) {
  // do your stuff
}