(SELECT 
ID 
The FROM 
T_Student201701) 
The UNION 
(SELECT 
ID 
The FROM T_Student201707) 
LIMIT 0, 
10; 

Such as access to the student record in 2017, a total of January and July two table
Need paging query and sort
So the query efficiency is extremely low, if each form a query second query.
How to solve?

Query plan as shown in figure


1 PRIMARY T_StudentAttendanceDetails201701 index ModifyTime 6 943851 Using index
2 the UNION T_StudentAttendanceDetails201707 index index_ModifyTime 6 1205527 Using index
The UNION RESULT & lt; Union1, 2 & gt; ALL the Using temporary

CodePudding user response:

PRIMARY T_StudentAttendanceDetails201701 index ModifyTime 6 943851 Using index


The UNION T_StudentAttendanceDetails201707 index index_ModifyTime 6 1205527 Using index

The UNION RESULT & lt; Union1, 2 & gt; ALL the Using temporary

CodePudding user response:

Top up...

CodePudding user response:

Union means to heavy, if you don't repeat two table data, with the union all

CodePudding user response:

Can also place the results to the union in advance to one table and then provides the result directly

CodePudding user response:

With is the union of all
Code to write wrong,
Efficiency is very low

CodePudding user response:

Look at the table structure, execution plan into a piece of, didn't move

CodePudding user response:

Forgot one thing, the select id from a union all select id from b, is a pit in MySQL
Limit the query should you bring to the subquery in, otherwise it first to calculate the subquery, then for the query results page

(SELECT
ID
The FROM
T_Student201701 LIMIT 0, 10)
The UNION
(SELECT
ID
The FROM T_Student201707 LIMIT 0, 10)
LIMIT 0, 10;

CodePudding user response:

 [code=csharp] [code=Java] [code=PHP] [code=Json] 

[/code] [/code] [/code] [/code]

CodePudding user response:

If the two table id is not the same, can change the union to union all,
If two tables have the same id, suggest to create a new table, storage to heavy result,

CodePudding user response:

If light query ID, should not be too slow, do you have other properties also take out?