問題描述

我正在嘗試為我的網站創建一個小型搜索.我曾嘗試使用全文索引搜索，但始終無法使用它.這是我想出的:

if(isset($_GET['search'])) {$search = str_replace('-', ' ', $_GET['search']);$result = array();$titles = mysql_query("SELECT title FROM Entries WHERE title LIKE '%$search%'");while($row = mysql_fetch_assoc($titles)) {$result[] = $row['title'];}$tags = mysql_query("SELECT title FROM Entries WHERE tags LIKE '%$search%'");while($row = mysql_fetch_assoc($tags)) {$result[] = $row['title'];}$text = mysql_query("SELECT title FROM Entries WHERE entry LIKE '%$search%'");while($row = mysql_fetch_assoc($text)) {$result[] = $row['title'];}$result = array_unique($result);}

所以基本上，它搜索數據庫中所有條目的所有標題、正文和標簽.這工作得很好，但我只是想知道它的效率如何?這也僅適用于小型博客.無論哪種方式，我只是想知道這是否可以提高效率.

沒有辦法使 LIKE '%pattern%' 查詢高效.獲得大量數據后，使用這些通配符查詢的執行速度比使用全文索引解決方案慢數百或數千倍.

你應該看看我為 MySQL 大學所做的演示:http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

以下是讓它工作的方法:

1. 首先確保您的表使用 MyISAM 存儲引擎.MySQL FULLTEXT 索引僅支持 MyISAM 表.(編輯 11/1/2012: MySQL 5.6 為 InnoDB 表引入了 FULLTEXT 索引類型.)

   ALTER TABLE Entries ENGINE=MyISAM;

2. 創建全文索引.

   CREATE FULLTEXT INDEX searchindex ON Entries(title, tags, entry);

3. 搜索吧！

   $search = mysql_real_escape_string($search);$titles = mysql_query("從條目中選擇標題WHERE MATCH(title, tags, entry) AGAINST('$search')");while($row = mysql_fetch_assoc($titles)) {$result[] = $row['title'];}

   請注意，您在 MATCH 子句中命名的列必須與您在全文索引定義中聲明的列順序相同.否則它不會工作.

<小時><塊引用>

我嘗試過使用全文索引搜索，但始終無法使用它...我只是想知道是否可以提高效率.

這就像在說，我不知道如何使用這個電鋸，所以我決定用小折刀砍掉這棵紅杉樹.我怎樣才能讓它和電鋸一樣好用?"

關于您對搜索匹配超過 50% 行的單詞的評論.

MySQL 手冊說這個:

需要繞過50%限制的用戶可以使用布爾搜索模式；參見 第 11.8.2 節，布爾全文搜索".

還有這個:

自然語言的 50% 閾值搜索由選擇了特定的加權方案.到禁用它，查找以下內容storage/myisam/ftdefs.h 中的一行:

#define GWS_IN_USE GWS_PROB

將該行更改為:

#define GWS_IN_USE GWS_FREQ

然后重新編譯MySQL.沒有必要在這種情況下重建索引.

此外，您可能正在搜索停用詞.這些是全文搜索忽略的詞，因為它們太常見了.諸如the"之類的詞.見 http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

I'm trying to create a small search for my site. I've tried using full-text index search, but I could never get it to work. Here is what I've come up with:

if(isset($_GET['search'])) {

$search = str_replace('-', ' ', $_GET['search']);
$result = array();

$titles = mysql_query("SELECT title FROM Entries WHERE title LIKE '%$search%'");
while($row = mysql_fetch_assoc($titles)) {
    $result[] = $row['title'];
}

$tags = mysql_query("SELECT title FROM Entries WHERE tags LIKE '%$search%'");
while($row = mysql_fetch_assoc($tags)) {
    $result[] = $row['title'];
}

$text = mysql_query("SELECT title FROM Entries WHERE entry LIKE '%$search%'");
while($row = mysql_fetch_assoc($text)) {
    $result[] = $row['title'];
}

$result = array_unique($result);
}

So basically, it searches through all the titles, body-text, and tags of all the entries in the DB. This works decently well, but I'm just wondering how efficient would it be? This would only be for a small blog, too. Either way I'm just wondering if this could be made any more efficient.

There's no way to make LIKE '%pattern%' queries efficient. Once you get a nontrivial amount of data, using those wildcard queries performs hundreds or thousands of times slower than using a fulltext indexing solution.

You should look at the presentation I did for MySQL University: http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

Here's how to get it to work:

1. First make sure your table uses the MyISAM storage engine. MySQL FULLTEXT indexes support only MyISAM tables. (edit 11/1/2012: MySQL 5.6 is introducing a FULLTEXT index type for InnoDB tables.)

   ALTER TABLE Entries ENGINE=MyISAM;
   

2. Create a fulltext index.

   CREATE FULLTEXT INDEX searchindex ON Entries(title, tags, entry);
   

3. Search it!

   $search = mysql_real_escape_string($search);
   $titles = mysql_query("SELECT title FROM Entries 
       WHERE MATCH(title, tags, entry) AGAINST('$search')");
   while($row = mysql_fetch_assoc($titles)) {
       $result[] = $row['title'];
   }
   

   Note that the columns you name in the MATCH clause must be the same columns in the same order as those you declared in the fulltext index definition. Otherwise it won't work.

I've tried using full-text index search, but I could never get it to work... I'm just wondering if this could be made any more efficient.

This is exactly like saying, "I couldn't figure out how to use this chainsaw, so I decided to cut down this redwood tree with a pocketknife. How can I make that work as well as the chainsaw?"

Regarding your comment about searching for words that match more than 50% of the rows.

The MySQL manual says this:

Users who need to bypass the 50% limitation can use the boolean search mode; see Section 11.8.2, "Boolean Full-Text Searches".

And this:

The 50% threshold for natural language searches is determined by the particular weighting scheme chosen. To disable it, look for the following line in storage/myisam/ftdefs.h:

#define GWS_IN_USE GWS_PROB

Change that line to this:

#define GWS_IN_USE GWS_FREQ

Then recompile MySQL. There is no need to rebuild the indexes in this case.

Also, you might be searching for stopwords. These are words that are ignored by the fulltext search because they're too common. Words like "the" and so on. See http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

這篇關于MySQL/PHP 搜索效率的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！