MySQL multi-table query + paging How to achieve optimal?

The question is not how to federate a query, but to see that some people say that it is best not to use multi-table federated queries for large data volumes, so it is optimal to know how to write SQL from multiple tables to get paginated data lists.
Add: If there are two tables: A table is the main table, and the data in B table is one of the filter criteria for querying a table

Reply content:

The question is not how to federate a query, but to see that some people say that it is best not to use multi-table federated queries for large data volumes, so it is optimal to know how to write SQL from multiple tables to get paginated data lists.
Add: If there are two tables: A table is the main table, and the data in B table is one of the filter criteria for querying a table

First identify the IDs that are recorded in the table, and then use the in query in another table

No specific business scenarios to talk about optimization are bullying ah.
In a table to increase the redundancy of table B, directly on the A-table query can be taken to a page of content, and the associated B table of other information can be directly taken, or through the ID of B table can be taken in the cache.

This optimization increases maintenance costs, but the lower the paradigm level, the higher the query efficiency.

For reference only.

There are some special business scenarios where you have to use a federated query. Cannot simply blindly optimize.
IDs in query, if IDs is out of tens of thousands of. It might as well be a direct union query. Although this plan is very good.

Optimization refers to the premise of not affecting the business, if only for optimization and optimization of the ╮(╯▽╰)╭

Pagination can refer to http://segmentfault.com/q/10100000001 ...

As for the optimization query, in essence, the first to optimize the data structure, think about whether you have to use two of tables, a row can not. And how much you know about the database built-in functions, such as Left (), right (), substring (), Substring_index (). There is also mid (), substr (). Do not be like and so on, mainly or write more, according to the actual application of the scene to make the best judgment.

By the way, the MySQL configuration itself can be a lot of optimizations, such as Query_cache and so on

