mysql的搜索效率_MySQL / PHP搜索效率

没有办法使LIKE’％pattern％’查询有效.一旦获得大量数据,使用这些通配符查询的速度比使用全文索引解决方案慢几百或几千倍.

以下是如何使其工作：

>首先确保您的表使用MyISAM存储引擎. MySQL FULLTEXT索引仅支持MyISAM表. (编辑11/1/2012：MySQL 5.6为InnoDB表引入了一个FULLTEXT索引类型.)

ALTER TABLE Entries ENGINE=MyISAM;

>创建全文索引.

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

>搜索它！

$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子句中命名的列必须与您在全文索引定义中声明的列的顺序相同.否则它将无法工作.

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.

这就像是在说：“我无法弄清楚如何使用这种电锯,所以我决定用小折刀砍下这棵红木树.我怎么能像电锯那样做这个工作呢？”

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

MySQL手册说this：

Users who need to bypass the 50% limitation can use the boolean search mode; see 07002.

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.