Split left join into multiple SQL statements in SQL to improve SQL performance

This article explains how to split an SQL statement with one or more left join or right join statements into multiple SQL statements. The efficiency of MySQL to query connected tables is very low, especially when the data volume is large and the concurrency is high, indexing cannot solve the problem, the best way is to split the SQL statement into multiple single-table queries.

Our company's e-commerce website is now going to serve the website, using java as the middleware, PHP calls the java interface to obtain data, and data tables are also split and split into databases, requiring no table connection query, you can use the java interface to split multiple SQL statements for table connection query.

The purpose of this operation is to make adjustments to the service-oriented website. Data addition, deletion, query, and modification are encapsulated in specific services. The second is to improve performance and reduce database pressure, to improve efficiency.

Let's take a look at how to split multiple SQL statements for one join multiple tables.

Select u1.uid, u1.uname, u2.add from user as u1 left join userinfo as u2 on u1.uid = u2.uid left join money as u3 on u1.uid = u3.uid where u1.country = 'C'

This SQL statement left join three tables, namely the user as the master table and the userinfo and money tables. First, we can find all the user data, with the first split SQL:

Select uid, uname from user where country = 'C'

Because the condition is user. uid = userinfo. uid, you can use "," to connect the obtained uid. The SQL statement for the second split is as follows:

Select uid, add from userinfo where uid in)

In this way, the uid, uname, and add fields that meet the first left join condition are obtained. In fact, the above left join statements have been met here, if you want to query the fields of the money table, and so on, you can use the uid as the in condition to query the money table.