MySQL/PHP搜索效率 [英] MySQL/PHP Search Efficiency

问题描述

我正在尝试为我的网站创建一个小型搜索.我曾尝试使用全文本索引搜索，但无法使用它.这是我想出的:

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 University做的演示文稿: 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("SELECT title FROM Entries 
       WHERE MATCH(title, tags, entry) AGAINST('$search')");
   while($row = mysql_fetch_assoc($titles)) {
       $result[] = $row['title'];
   }
   

   请注意，您在MATCH子句 中命名的列必须与在全文索引定义中声明的列相同，并且顺序相同.否则它将无法正常工作.

---

我曾经尝试使用全文本索引搜索，但是我无法使其正常工作……我只是想知道这样做是否可以提高效率.

这就像在说:我不知道如何使用这把电锯，所以我决定用小刀砍掉这棵红木树.我如何使它和电锯一样好用?"

---

关于您搜索匹配行数超过50％的单词的评论.

MySQL手册说此:

需要绕过50％限制的用户可以使用布尔搜索模式；参见第11.8.2节布尔全文搜索" .

和此:

自然语言的50％门槛 搜索由 选择特定的加权方案.到 禁用它，查找以下内容 存储/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搜索效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！