WHERE IN SELECT: Prompt an optimal variant of interaction PHP — MySQL

Hello.

How to make the right how to make better?

Option 1:

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?

Option 2:

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?

Thanks for the replies!
October 8th 19 at 03:47
8 answers
October 8th 19 at 03:49
>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
October 8th 19 at 03:51
personally, I want a inner joib because in (version 6 for sure) is optimized as depend subquery.
bugs.mysql.com/bug.php?id=9090. - nasir17 commented on October 8th 19 at 03:54
Thanks, I'll try the INNER JOIN
Although JOINS s me very confused, because often read articles on optimization in which it is not recommended to use them. - easton.Schmel commented on October 8th 19 at 03:57
inner join != left join. inner join synonym list the tables separated by commas:
select * from tableA join tableB using (someFiled)
and
select * from tableA, tableB
where tableA.someField = tableB.someField
These two entries are synonyms, so that the inner joins should not be afraid. - aniya.Kertzmann65 commented on October 8th 19 at 04:00
inner join != left join, by definition.
I don't see why need to be scared of left join.

P. S. If inner in the result table can not get all records. - davonte commented on October 8th 19 at 04:03
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.
There is some limit on the query length, the number of IDs in the IN()? - nasir17 commented on October 8th 19 at 03:56
Both constraints are. Exactly. see the documentation for the database. see max_allowed_packet, net_buffer_length
I did option 2 and rested on them. - easton.Schmel commented on October 8th 19 at 03:59
October 8th 19 at 03:55
Actually, when I started about arhitektutu, then just do this:
tableB->getAll();
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.
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.
About

> The question arises: if the first request will be 1k IDs?

Well in this case anyway, you have code that these records will display in the template will inhibit much stronger than code fetch 1000 records from database. So don't worry.

When using complex queries, the risk of unexplained brake increases significantly. - nasir17 commented on October 8th 19 at 04:00
>can be done cheap an optimization of the allocation tables on different servers
really want to know about the project in which it still needed to be done. - easton.Schmel commented on October 8th 19 at 04:03
> I really want to know about the project in which it is still needed to make

What's wrong with this option? We have a project (I think not even one), where the database server is overloading (even with memcache, correct indexes and other things, as there is a second lot of hits and large sizes of tables, lots of users) and tables scattered across several servers. What is the project, do not want to call. - aniya.Kertzmann65 commented on October 8th 19 at 04:06
we have a secret devices, but we about them do not tell. clear. - davonte commented on October 8th 19 at 04:09
If you have a project where the problem starts with the load on the database, you can test this method and verify that it works, with the right approach. If you have a problem there, meaning to discuss something? To prove that 2 or 3 servers can handle more requests than 1, I don't see the point. - easton.Schmel commented on October 8th 19 at 04:12
>To prove that 2 or 3 servers can handle more requests than 1
which is a pity. because it usually turns Vice versa, and two or three servers are noticeably slower than one, writing an abstraction layer of that server takes an unrealistic time and administer it impossible. - Ophelia_Run commented on October 8th 19 at 04:15
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
join
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.
October 8th 19 at 04:01
>and if the first request will be 1k IDs?
and check what will happen?
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.

Find more questions by tags PHPMySQL