I have a messages field in my database, I want to search for a particular string in the users messages in the fastest way. Since the user has got many messages, the process might be slow. This is the sql query that I have so far:
我的数据库中有一个消息字段，我想以最快的方式在用户消息中搜索特定的字符串。由于用户收到了很多消息，因此进程可能很慢。这是我到目前为止的SQL查询：
$result=mysql_query("
SELECT message_content
FROM `messages` AS ms
INNER JOIN users1 AS us ON us.user_id=ms.user_id
WHERE us.user_id=".$user_id." AND message_content LIKE '%".$string_to_search."%'") or die(mysql_error());
Is there a way to speed up the search?
有没有办法加快搜索速度？
1 个解决方案
#1
3
String searching is an inherently problematic venture with databases. Using LIKE is the most portable way to do it; it certainly searches your strings. That implies that it reads every record and scans for the given substring, which is the very slowest way you can search a database.
字符串搜索在数据库方面存在问题。使用LIKE是最便携的方式;它肯定会搜索你的字符串。这意味着它会读取每个记录并扫描给定的子字符串，这是搜索数据库的最慢方式。
An alternative in MySQL is to use FULLTEXT searches, which are index-driven but have a number of restrictions imposed on them. It's better, but it's not the sort of thing you'd want to build a search engine on.
MySQL中的另一种选择是使用FULLTEXT搜索，这些搜索是由索引驱动的，但是对它们施加了许多限制。它更好，但它不是你想要建立一个搜索引擎的东西。
On the other hand, projects like Sphinx ARE in fact designed for running a search engine, and do it very well. Sphinx ties pretty well into MySQL and was built specifically for solving this problem. It offers very good search accuracy and performance, so if you're doing anything serious, it's the way to go.
另一方面，像Sphinx ARE这样的项目实际上是为运行搜索引擎而设计的，而且做得很好。 Sphinx与MySQL紧密相关，专门为解决这个问题而构建。它提供了非常好的搜索准确性和性能，所以如果你正在做任何严肃的事情，那就是要走的路。
#1
3
String searching is an inherently problematic venture with databases. Using LIKE is the most portable way to do it; it certainly searches your strings. That implies that it reads every record and scans for the given substring, which is the very slowest way you can search a database.
字符串搜索在数据库方面存在问题。使用LIKE是最便携的方式;它肯定会搜索你的字符串。这意味着它会读取每个记录并扫描给定的子字符串，这是搜索数据库的最慢方式。
An alternative in MySQL is to use FULLTEXT searches, which are index-driven but have a number of restrictions imposed on them. It's better, but it's not the sort of thing you'd want to build a search engine on.
MySQL中的另一种选择是使用FULLTEXT搜索，这些搜索是由索引驱动的，但是对它们施加了许多限制。它更好，但它不是你想要建立一个搜索引擎的东西。
On the other hand, projects like Sphinx ARE in fact designed for running a search engine, and do it very well. Sphinx ties pretty well into MySQL and was built specifically for solving this problem. It offers very good search accuracy and performance, so if you're doing anything serious, it's the way to go.
另一方面，像Sphinx ARE这样的项目实际上是为运行搜索引擎而设计的，而且做得很好。 Sphinx与MySQL紧密相关，专门为解决这个问题而构建。它提供了非常好的搜索准确性和性能，所以如果你正在做任何严肃的事情，那就是要走的路。