WHERE IN SELECT: Prompt an optimal variant of interaction PHP — MySQL
How to make the right how to make better?
The result of the first query:
SELECT `ID` FROM `TABLE_A'WHERE `FIELD_C` = '123';
Substitute the RES-t of the first query to the second query:
SELECT * FROM `TABLE_B` WHERE `FIELD_A` IN ('result of 1st query');
Total transactions: 2
The question arises: if the first request will be 1k IDs?
SELECT * FROM `TABLE_B` WHERE `FIELD_A` IN (SELECT `ID` FROM `TABLE_A'WHERE `FIELD_C` = '123');
Total transactions: 1
The question arises: is this query hard?
The point of my question is the following: in articles about the architecture of highly loaded systems (such as twitter) is recommended for the interaction with the database using simple queries, and generating a result of conduct on the compute servers (easier to scale).
So, what is better:
— Option 1 with two simple queries to the database, but perhaps difficult to parse the second request, if the number of transmitted identifiers, such as a thousand?
— 2 variant with a single query, but nested?
>articles about architecture of highly loaded systems (e.g.: twitter)
unless you have loads such as Twitter, do not do so. only complicate their lives, writing their own joynow php
nasir17 answered on October 8th 19 at 03:51
personally, I want a inner joib because in (version 6 for sure) is optimized as depend subquery.
eloise_Hahn answered on October 8th 19 at 03:53
If the tables are of the same type (this applies to fields and engines) not used (not effective) caching, it is better option 2.
In other cases, option 1.
aniya.Kertzmann65 answered on October 8th 19 at 03:55
Actually, when I started about arhitektutu, then just do this:
Inside everything will work yet You should not worry, the main thing that this method (function) return vsegda the same data. Then when (if?) will slow down, or You get that not to make You this method will alter as your heart desires.
So no matter what you do, the main highlight such contentious issues in one place, which vsegda returns the same data as if You did not change anything.
antonette.Gislas answered on October 8th 19 at 03:57
If you choose the second option, can be done cheap an optimization of the allocation tables on different servers. That is, in this case load increases will have to buy disk arrays, multiprocessor kernel and other crap. And then break the hands of the architect and rewrite all the code.
In the first case, corny post of the table for the servers and another year idle.
Also, with the first option, entities can pull partially from the cache by multibedroom. A second version of hell zacchiroli.
So don't listen to Joyner, then sorry for you.
Ophelia_Run answered on October 8th 19 at 03:59
I'm for option 2. if there is no option 3.
Option 1 can sometimes not work due to hitting the limits:
you can get 2 limit
1. exceeding the maximum request length
2. exceeding the maximum number of parameters IN. Yes, there are some limitations
There are limitations not only in muscle but also in the Oracle, postrges and other databases.
Option 2 is still the same and the brakes, if we have a lot of records in the outer query.
Simple queries will work faster:
select * from table1,table2 where conditions
or request via
select * from table1 join table2 on condition where condition
which of them will run faster see for yourself. The settings of the database rasp for data and the queries that will be executed on these data.
valli answered on October 8th 19 at 04:01
>and if the first request will be 1k IDs?
and check what will happen?
constance.Bei answered on October 8th 19 at 04:03
The purpose of these optimizations is to make the database do as little as possible, and the application that runs with the base as much as possible. Because the database is difficult to scale, and the servers on which revolves the application add easily.
If you have one server under application and under database, such optimization is harmful because the app will do faster what will make the base in most cases. Therefore, it is necessary to use inner join.
If You are interested in academic considerations or ready to purchases of new servers, the first option is better.