哪种SQL查询更好，匹配还是类似?

To search the database for rows that have both keywords "foo" AND "bar" in any of the columns "foo_desc" and "bar_desc", I would do something like:

为了在“foo - desc”和“bar_desc”的任何列中搜索具有“foo”和“bar”两个关键字的行，我将做如下操作:

SELECT * 
FROM t1 
WHERE MATCH (t1.foo_desc, t2.bar_desc) AGAINST ('+foo* +bar*' IN BOOLEAN MODE)

or

或

SELECT * 
FROM t1 
WHERE (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%foo%') AND (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%bar%')

I expect the downside of the last query is performance.

我预计最后一个查询的缺点是性能。

The upside is that the LIKE query finds 'xxfoo' where MATCH AGAINST does not.

好处是，LIKE查询会找到'xxfoo'，而MATCH AGAINST则不会。

Which is the preferred one or is there a better solution?

哪一个是首选的还是有更好的解决方案?

1 个解决方案

#1

Update

As of MySQL 5.6 and later, InnoDB tables supports Match... Against.

从MySQL 5.6到以后，InnoDB表支持Match…反对。

---

The first is much better. On MyISAM tables it will use a full text index against those columns. The other will do a full table scan doing a concat on every row and then a comparison.

第一种更好。在MyISAM表中，它将对这些列使用完整的文本索引。另一个将对每一行进行全表扫描，然后进行比较。

LIKE is only efficient if you're doing it against:

喜欢只有在你反对的时候才有效:

• a column (not a result of a function unless your particular database vendor supports functional indexes--Oracle, for example--and you're using them);
• 列(不是函数的结果，除非您的特定数据库供应商支持函数索引——例如Oracle——并且您正在使用它们);
• the start of the column (ie LIKE 'blah%' as opposed to LIKE '%blah%'); and
• 列的开头(比如“blah%”，而不是“%blah%”);和
• a column that's indexed.
• 一个列的索引。

If any one of those conditions are not true the only way for the SQL engine to execute the query is by doing a full table scan. This can be usable under about 10-20 thousand rows. Beyond that it quickly becomes unusable however.

如果这些条件中的任何一个都不正确，那么SQL引擎执行查询的唯一方式就是执行完整的表扫描。这可以在大约10-2万行的范围内使用。除此之外，它很快就无法使用了。

Note: One problem with MATCH on MySQL is that it seems to only match against whole words so a search for 'bla' won't match a column with a value of 'blah', but a search for 'bla*' will.

注意:与MySQL匹配的一个问题是，它似乎只匹配整个单词，所以搜索“bla”不会匹配值为“blah”的列，而搜索“bla*”将匹配。