MySQL全文搜索vs Like %%

时间:2022-12-31 10:36:43

What are key differences and what is a use case for both of them? Thanks!

什么是关键差异以及它们的用例是什么?谢谢!

1 个解决方案

#1


20  

Full-text search is the kind of search based on special sort of index (full-text, obviously). So you get the power of O(lgN) while searching using it.

全文搜索是一种基于特殊索引的搜索(显然是全文)。因此,在使用它时,您可以获得O(lgN)的强大功能。

While like %% always causes table fullscan which can be terrible slow (when you have 100k and more rows).

虽然像%%总是导致表fullscan可能很慢(当你有100k和更多行)。

Personally I use Like %% when it is a small table (0-1000 rows) and I'm sure that it will never grow (and, important, when like %% fits the task requirements).

我个人使用Like %%,当它是一个小表(0-1000行)时,我确信它永远不会增长(而且,重要的是,当%%符合任务要求时)。

Note: fulltext indexes are available only for myisam SE. If you use innodb - then you need to look at some 3rd party indexing software like sphinx

注意:全文索引仅适用于myisam SE。如果您使用innodb - 那么您需要查看一些第三方索引软件,如sphinx

#1


20  

Full-text search is the kind of search based on special sort of index (full-text, obviously). So you get the power of O(lgN) while searching using it.

全文搜索是一种基于特殊索引的搜索(显然是全文)。因此,在使用它时,您可以获得O(lgN)的强大功能。

While like %% always causes table fullscan which can be terrible slow (when you have 100k and more rows).

虽然像%%总是导致表fullscan可能很慢(当你有100k和更多行)。

Personally I use Like %% when it is a small table (0-1000 rows) and I'm sure that it will never grow (and, important, when like %% fits the task requirements).

我个人使用Like %%,当它是一个小表(0-1000行)时,我确信它永远不会增长(而且,重要的是,当%%符合任务要求时)。

Note: fulltext indexes are available only for myisam SE. If you use innodb - then you need to look at some 3rd party indexing software like sphinx

注意:全文索引仅适用于myisam SE。如果您使用innodb - 那么您需要查看一些第三方索引软件,如sphinx