mysql 在 join 和 union 和/或其他之间的搜索速度
我正在尝试创建一个搜索功能，用户可以在其中输入单词或关键短语，然后显示信息。
我正在考虑使用 LEFT JOIN 来添加我需要可搜索的所有表，有人告诉我有关 UNION 的信息，我有预感它可能比 JOIN 慢>
那么
$query = '
SELECT *
FROM t1
LEFT JOIN t2
ON t2.content = "blabla"
LEFT JOIN t3
ON t3.content = "blabla"
[...]
WHERE t1.content = "blabla"
';
上述是一个好的做法还是我应该研究更好的方法？
让我走上正确的道路:)也争论为什么它是错误的，争论为什么你认为你的方法更好，这样它将帮助我和其他人理解这一点：
I am trying to create a search functionality where users would type a word or key phrase and then information is displayed.
I was thinking of using the LEFT JOIN to add all the table i need to be searchable,someone has told me about UNION and I have a hunch that it may be slower than JOIN
so
$query = '
SELECT *
FROM t1
LEFT JOIN t2
ON t2.content = "blabla"
LEFT JOIN t3
ON t3.content = "blabla"
[...]
WHERE t1.content = "blabla"
';
Is the above a good practice or is there a better approach i should be looking into ?
Send me on the right path for this :) also argue why its wrong, argue why you think your approach is better so it will help me and other understand this:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一般来说，凭直觉“猜测”SQL 引擎的性能是一个坏主意。其中发生了非常复杂的优化，其中考虑了表的大小、索引的可用性、索引的基数等等。
在此示例中，LEFT JOIN 是错误的，因为您正在生成半笛卡尔 JOIN。基本上，结果集中的行数比您想象的要多很多。这是因为 t1 中的每个匹配行都将与 t2 中的每个匹配行连接。如果 t1 中有 10 行匹配，t2 中有 3 行匹配，那么您将不会得到 10 个结果，而是 30 个结果。
即使每个表中只保证一行匹配（消除笛卡尔连接问题），很明显，LEFT JOIN 解决方案将为您提供一个非常难以使用的数据集。这是因为您加入的每个表中的内容列将是结果集中的单独列。您必须检查每一列才能找出匹配的表。
在这种情况下，UNION 是一个更好的解决方案。
另外，请注意：
在 SELECT 中使用“*”通常不是一个好主意。它会降低性能（因为所有列都必须组装在结果集中），并且在这种情况下，您将失去对每个内容列进行别名的机会，从而使结果集更难以使用。
这是 LEFT JOIN 的一种非常新颖的用法。通常，它用于关联两个不同表中的行。在本例中，您将使用它“并排”生成三个单独的结果集。大多数 SQL 程序员必须仔细查看该语句一段时间才能弄清楚您的意图。
In general, it's a bad idea to play hunches to "guess" what the performance of an SQL engine will be like. There is very sophisticated optimization happening in there which takes into account the size of the tables, the availability of indexes, the cardinality of indexes, and so on.
In this example, LEFT JOIN is wrong because you're producing a semi-cartesian JOIN. Basically, there will be a lot more rows in your result set than you think. That's because each matching row in t1 will be joined with each matching row in t2. If ten rows match in t1 and three in t2, you will not get ten results but thirty.
Even if only one row is guaranteed to match from each table (eliminating the cartesian join problem) it's clear that the LEFT JOIN solution will give you a dataset that's very hard to work with. That's because the content columns from each of the tables you JOIN will be separate columns in the result set. You'll have to examine each of the columns to figure out which table matched.
In this case, UNION is a better solution.
Also, please note:
Use of "*" in SELECT is generally not a good idea. It reduces performance (because all columns must be assembled in the result set) and in a case like this you lose the opportunity to ALIAS each of the content columns, making the result set harder to work with.
This is a very novel use of LEFT JOIN. Normally, it's used to associate rows from two different tables. In this case you're using it to produce three separate result sets "side-by-side". Most SQL programmers will have to look at this statement cross-eyed for a while to figure out what your intent was.