CODEWITHSUNDEEP
phpmysqlfull-text-search
user1254541
user1254541

Reputation:

How can I improve this mysql query for fulltext search?

I use the following query for searching my table. But it is very slow, how can fast or improve it?

    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);
    }`

Upvotes: 0

Views: 184

Answers (1)

ashein
ashein

Reputation: 487

Did you declare your full-text indices? The query seems simple, so maybe an unindexed search cripples your database.

As for the query itself, I prefer forming the WHERE clause this way, saves a lot of hassle. And the query is much more readable.

// 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
}

Upvotes: 1

Related Questions