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?
The result will be used in a PHP script.
Queries:
SELECT (let's call it Query A) will always yield 10 rows, 4 columns no matter what.SELECT (Query B) will always yield 1 row, 4 columns no matter what.Scenarios:
SELECT the two statements, I will iterate Query B only if Query A[0]->Col1 > 0 : total of 2 db queries + PHP processing.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.Non-negotiables:
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.
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
There are tons of article out there explaining for you how to use Explain. here is one.
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.