Three tables associated
Select
The sum (XXX)
From a
Left the join b on Anderson, d1=b.i d1 and Anderson, d2=b.i d2
Left the join c on c.i d1=b.i d1
Where a. d. ate between 'and' scope for a month (time) and a.org (1,2,3,4,5,6,7,8,9,10) and in b.l og=0
Group by
B.i b.i b.i b.i d3, d4, d5, d6, b.i d7, b.i d8 (branch field is 6)

The query time: 5.658

The largest amount of data in the table b, within the scope of this time for 392571 data, the whole table data volume for 2989801 data,
After the explain

Id select_type tabel type rows extra
1 SIMPLE reaching 45113 Using a where clause; Using the index; Using temporary; Using filesort
1 SIMPLE ref 8 b Using the where
1 SIMPLE c eq_ref 4 Using the index

Show profile block IO, CPU for query 449;
The status duration cpu_user cpu_system Block_ops_in Block_ops_out
Copying to TMP table 4.831741 4.890625 0 null null

Grouping is a must, need according to the group to determine a lot of data in the result set, and the six field is the basis of a group, there is no other way to deal with,
Even remove the packet directly query the rest, the time is more than 3 seconds,

Mysql version: 5.5
Temp_table_size of 35 m, max_heap_table_size this in the configuration file not found, there is no any changes to this do,
I'm on the amount of data is smaller than the library temp_table_size the value of the transferred to 50 m and 100 m, and no obvious ascension,

Hope the great god help some advice optimization,

PS:
Behind the where condition is determined according to the parameters, the date and org there will be, but the time range, org in content is also uncertain, have already done according to the org table partitioning, single to check an org, a month's worth of data 0.704 s can a result,

CodePudding user response:

Also encountered similar problems, to solve

CodePudding user response:

I big CSDN nobody help me,,,

CodePudding user response:

For such a long time, and no one helping to see ah, I posted the information is full enough

CodePudding user response:

A table in the temporary table and sorting,
Will group by implied a order by sorting the syntax

CodePudding user response:

Table index structure?

Retrieve the data quantity is how many? (there is no group by)


The total number of three tables according to quantity?

CodePudding user response:

A b c table index, and related fields in the table have a repeat?

CodePudding user response:

I met a lot of development colleagues write SQL, is to use related queries, but don't know whether need to use the association or correlation, also, you also need to determine whether you need to use the left associated query, this is very important, if the business need to use the connection on the left,