Because MySQL does not optimize by any index when the query contains a full-text search, any ORDER BY's slow the query significantly. To get around that, we are currently taking advantage of subqueries and an extra table.
We're using tables search_contents and search_results. The former has all our unique articles as usual while the latter is a table we're dumping only article id, searchID, issuedate, and relevancy score. The issuedate and score is so that we can do an ORDER BY on either one after the full-text search has been performed. Here's the initial query:
$strSearch = "INSERT INTO search_results(articleID, issuedate, score) SELECT articleID, issuedate, ( (1.3 * (MATCH(title) AGAINST ('$_SESSION[search_term]' IN BOOLEAN MODE))) + (0.6 * (MATCH(abstract) AGAINST ('$_SESSION[search_term]' IN BOOLEAN MODE))) ) AS score FROM $fromTable AS sc WHERE MATCH(title, abstract) AGAINST ('".$this->_searchterms."' IN BOOLEAN MODE) LIMIT 800";
The $fromTable that the full-text search is looking in is search_contents reduced down to only one month of articles at a time so that the limit of 800 articles is sufficient to pick up all the relevant articles. A user can go back another month and so on at anytime by clicking "More Results". It looks like:
$fromTable = "(SELECT * FROM search_contents WHERE issuedate >= '$oldestTime' && issuedate < '$newestTime')";
Finally, we pull each page's results with the following query (sorted by issuedate):
$strSearch = "SELECT s.articleID, s.title, s.subhead, s.abstract, UNIX_TIMESTAMP(s.issuedate) issuedate, sr.score FROM search_results sr, search_contents s WHERE sr.searchID = '$_SESSION[search_id]' AND s.articleID = sr.articleID ORDER BY sr.issuedate DESC LIMIT " . $this->_recordcount . ", 20";
Do you have any suggestions or ideas on both increasing speed and performance (the average initial query takes anywhere from 4 seconds to 20 seconds)?
Thanks
-Jeff
We're using tables search_contents and search_results. The former has all our unique articles as usual while the latter is a table we're dumping only article id, searchID, issuedate, and relevancy score. The issuedate and score is so that we can do an ORDER BY on either one after the full-text search has been performed. Here's the initial query:
$strSearch = "INSERT INTO search_results(articleID, issuedate, score) SELECT articleID, issuedate, ( (1.3 * (MATCH(title) AGAINST ('$_SESSION[search_term]' IN BOOLEAN MODE))) + (0.6 * (MATCH(abstract) AGAINST ('$_SESSION[search_term]' IN BOOLEAN MODE))) ) AS score FROM $fromTable AS sc WHERE MATCH(title, abstract) AGAINST ('".$this->_searchterms."' IN BOOLEAN MODE) LIMIT 800";
The $fromTable that the full-text search is looking in is search_contents reduced down to only one month of articles at a time so that the limit of 800 articles is sufficient to pick up all the relevant articles. A user can go back another month and so on at anytime by clicking "More Results". It looks like:
$fromTable = "(SELECT * FROM search_contents WHERE issuedate >= '$oldestTime' && issuedate < '$newestTime')";
Finally, we pull each page's results with the following query (sorted by issuedate):
$strSearch = "SELECT s.articleID, s.title, s.subhead, s.abstract, UNIX_TIMESTAMP(s.issuedate) issuedate, sr.score FROM search_results sr, search_contents s WHERE sr.searchID = '$_SESSION[search_id]' AND s.articleID = sr.articleID ORDER BY sr.issuedate DESC LIMIT " . $this->_recordcount . ", 20";
Do you have any suggestions or ideas on both increasing speed and performance (the average initial query takes anywhere from 4 seconds to 20 seconds)?
Thanks
-Jeff
