Ask the great God to tell you how to avoid database query optimization by using join query when database data is large.

Boss said that the usual query data large data to avoid using the join would rather once the data of a table to find out to use this data to do the query and do not use too much of the join as much as possible into the query to do, please the big God to say that the SQL optimization

Reply content:

Boss said that the usual query data large data to avoid using the join would rather once the data of a table to find out to use this data to do the query and do not use too much of the join as much as possible into the query to do, please the big God to say that the SQL optimization

If you use a relational database, it is a natural practice to join the table, unless there are some exceptions:

1. SQL statement is too complex, or inaccurate statistics, resulting in a database generated execution plan is not correct, resulting in inefficient operation, and a short time to rewrite the SQL

2. Data volume is particularly large (at least billion), database load becomes the performance bottleneck of the whole system, when the architecture design, the table connection function without the database itself is stipulated.

3. Using the ORM Framework, the N+1 query is used by default for associative objects, and is not inefficient if the amount of data is not particularly large and has a caching capability.

Therefore, in general, with the database of the join function, more than their own multiple data to do correlation efficiency is high, or a large data scale, but passed to PHP, it will take a long time.

The most basic principle of SQL optimization is to allow the database to filter data early and efficiently, avoid invalid operations, more specific means, need to be based on different databases to determine the implementation of the scheme.

Please do explain it yourself.

This depends on whether your database link is a long link, if configured as a long link is a little bit better, but to join the time can be first through where or other methods to reduce your main table, not a brain pull up, so the performance will be very good. But the specific problem of the specific analysis, run once there is a result. However, if it is not a long link, it is estimated that even the table will be faster.

The reason for being divided into multiple queries is that the MySQL optimizer optimizes SQL for both joins and subqueries (subqueries are smart to do bad things, and joins occasionally)
So a piece of writing, I can grasp

Join way is actually very good, mainly is the index grasp is not so handy, easy to appear problems.

So your boss directly says join is not for big data.

And the reasons for the separate check: for example, I get the corresponding side of the primary key ID, and then to another table to check, the efficiency is definitely high not to be.

Bottom line: Simpler queries are more efficient

I don't understand. If the data volume is large, the join will lead to poor performance, and if the amount of data is large to query a table alone on hundreds of MB, loading into the program takes a long time

1. Indexing speeds up queries

2. Leveraging caching (Memchache, etc.)

