Select a. *, b. *
The from (select * from test1) a
Left jioin (select * from test2) b on b.i d=Anderson d

Table a and table b the amount of data is very large, particularly slow query speed

CodePudding user response:

First consideration should be given in the where and order by involving the index on the column, as far as possible within the subquery conditions do not use a full table scan conditions, such as do not use!=or & lt;> Do not use or to connect to, conditions and so on, or connection, if necessary please amend for the union to connect, actually the conditions of the ultimate goal is to avoid a full table scan

CodePudding user response:

Suggested that the building Lord give more full query and predicate sentences,

Look at the statistics and execution plan,

CodePudding user response:

First of all, your query can be written as:

 SELECT a. *, b. * 
The FROM test1 a 
LEFT the JOIN test2 ON b.i b d=Anderson d 

Second, you use the left outer join, and there is no the WHERE condition, means that all data will be returned in the test1, at the same time table is very large, so slowly,

Suggest business and add some constraints, if must query a lot of data to the front-end display, consider using a paging display; Also confirm the associated fields (id) to create the indexes; And the actual project, don't use * returns all the fields, only output the information you need,

CodePudding user response:

If only if the display list after connection, should not give pressure to the database, make the paging display, SQL statements only take a table of a page, and then in the binding of a table this page data, according to the ID to get b table data, so not is fast?

CodePudding user response:

It is a external connection, so join order already can't adjust, are you sure this connection is necessary?
If outside connection if necessary, give the driver table test1 and test2 table amount of data, the amount of data that the query as well as the final output

CodePudding user response:

Typically do not select a. *, * and b.. select you the required fields

CodePudding user response:

First want to good what to query the data? Two Zhang Dabiao the full amount of the query, found out so much data to see come over? Without any conditions? All fields to see? Not likely

CodePudding user response:

Can try inside connection query: select a *, b * from the test, a test b where Anderson, d=b.i d try not to use sub queries, affect the efficiency,

CodePudding user response:

refer to the eighth floor jacljh response:

can try inside connection query: select a *, b * from the test a and test b where Anderson, d=b.i d try not to use sub queries, affect the efficiency of

6th floor question is, if you watch too many fields a *, b * may also affect efficiency, you need to check what the value of the field that several specific field is good, do not use *

CodePudding user response:

The two tables with view on the query to the view, select view again, soon

CodePudding user response:

3/f said is right, don't use a subquery, built on the back of the two fields index, check up fast, try the original poster

CodePudding user response:

This is more than one table problems, give it a try "how to optimize performance of the JOIN" this article can help you