I have 3 tables, respectively is logistics in and out of the table (stock_flow), the user table (user), goods table (product),
Now you need to link what query for each user and outbound goods,

Stock_flow table structure
Id primary key
Uid user ID
ProductID commodity ID

The user table structure
Id primary key
The username name

The product table structure
Id primary key
The title product name


I write my own SQL statements

 SELECT 
A. *, 
B.t itle, 
C.u sername 
The FROM 
(select * from stock_flow GROUP BY the uid, productID) a 
JOIN the product b ON Amy polumbo roductID=b.i d 
Left the join user c ON a.u id=c.i d 
The order by a.u id ASC 

This basic result, I will
And then explain the
The product table Extra is Using temporary; Using filesort
The user table Extra is Using the where; Using the join buffer (Block Nested Loop)

It is not clear how to optimize, hope comment!

CodePudding user response:

In the column and associated conditions index
DISTINCT replacement GROUP BY

 SELECT 
A. *, 
B.t itle, 
C.u sername 
The FROM 
(select DISTINCT uid, productID from stock_flow) a 
JOIN the product b ON Amy polumbo roductID=b.i d 
Left the join user c ON a.u id=c.i d 
The order by a.u id ASC 

CodePudding user response:

reference 1/f, Chinese wind response:

column plus index
the condition of associatedDISTINCT replacement GROUP BY

 SELECT 
A. *, 
B.t itle, 
C.u sername 
The FROM 
(select DISTINCT uid, productID from stock_flow) a 
JOIN the product b ON Amy polumbo roductID=b.i d 
Left the join user c ON a.u id=c.i d 
The order by a.u id ASC 

Brothers, so don't make the situation worse stock_flow Extra appear Using the index for group - by, other did not change

CodePudding user response:

Can't be worse, explain to the full

CodePudding user response:

reference 4 floor Chinese wind response:

can't be worse, the explain sun map