2个SELECT语句与UNION的性能/效率与MySQL-PHP中的其他任何性能/效率 [英] Performance/efficiency of 2 SELECT statements vs UNION vs anything else in MySQL-PHP

问题描述

在MySQL(使用PHP)中，哪个更有效/更好的实践/更便宜? SELECT将两个独立的语句，UNION合并为一个，或其他我在网上发现的关于CREATE将临时表作为SELECT INTO的MySQL等效项的东西?还是您能想到的其他东西?

Which is more efficient/better practice/less costly in MySQL (with PHP)? SELECTing two separate statements, UNIONing them into one, or something else I found online about CREATEing a temporary table as the MySQL equivalent of SELECT INTO? Or anything else you can think of?

结果将在PHP脚本中使用.

The result will be used in a PHP script.

查询:

• 第一个SELECT(我们将其称为查询A)无论如何都将始终产生10行，4列.
• 第二个SELECT(查询B)无论如何都将始终产生1行，4列.

• The first SELECT (let's call it Query A) will always yield 10 rows, 4 columns no matter what.
• The second SELECT (Query B) will always yield 1 row, 4 columns no matter what.

场景:

• 如果我SELECT这两个语句，则仅当Query A[0]->Col1 > 0:总共2个数据库查询+ PHP处理时，才会迭代查询B.
• 如果我以查询B的第一行(Query C[0]是查询B)和查询A的第2-11行(Query C[1..10]是查询A)的方式将这两个语句放入查询C， )，如果Query C[0]->Col1 > 0:总共1个数据库查询+ PHP处理，我将仅迭代2-11行.

• If I SELECT the two statements, I will iterate Query B only if Query A[0]->Col1 > 0 : total of 2 db queries + PHP processing.
• If I UNION the two statements into Query C in such a way that Query B will be the first row (Query C[0] is Query B) and Query A will be rows 2-11 (Query C[1..10] is Query A), I will only iterate rows 2-11 if Query C[0]->Col1 > 0 : total of 1 db query + PHP processing.

不可转让:

• 无论如何我都必须遍历查询A.
• 查询A的迭代取决于查询B的值.

少1个SELECT查询是否值得UNION操作?有没有更有效/更好的做法/成本更低的解决方案?我在这里想念什么吗?预先感谢.

Is the 1 less SELECT query worth the UNION operation? Is there a more efficient/better practice/less costly solution to this? Anything I'm missing here? Thanks in advance.

推荐答案

这完全取决于环境和您管理的方式-我不得不说-其他所有内容.如果服务器位于地球另一端的另一个网络上，则网络是您的瓶颈.
为了进行更好的调用，我会做一些假设:服务器位于localhost上，连接得到了正确的管理，只是查询本身应该被优化.如果是这样，您可以按照Explain命令使用>这里非常简单:

It totally depends on the environment and the way you are managing- I have to say- everything else. If the server is located on another network, on the other side of the planet, network is your bottleneck.
To make a better call I would make some assumptions: the server is on localhost, the connections are managed properly, and it is just the query itself that should be optimized. If that's the case for you, you could use Explain command the usage as explained here is very simple:

Explain select col1 from table1 Union select col2 from table2

有大量文章为您解释如何使用Explain. 此处是一个.

There are tons of article out there explaining for you how to use Explain. here is one.

最后一件事，重要的不仅仅是行数.同样重要的是您的选择代码也要执行多少次.假设您有一个循环，该循环在每次运行时返回11行，并具有1000次迭代.只需假设每次迭代中的行都不同，那么您将看到连接管理和缓存的效果.

One last thing, it is not just the number of rows that matter. It is also important how many times your select code is being executed too. Assume you have a loop which returns 11 rows on each run and has 1000 iterations. Just assume that the rows are different in each iteration, then you will see the effect of your connection management and caching.

这篇关于2个SELECT语句与UNION的性能/效率与MySQL-PHP中的其他任何性能/效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！