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 大学所做的演示: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 搜索效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！