Mysql optimization problem Usingwhere; Usingfilesort explain select userid from team_enewsmember where agentid in (select userid from team_enewsmember where agentid = '2' and levelid> = 1 group by userid) and levelid> = 1 group by userid
If the primary key userid and agentid are combined with the index, the Using where; Using index will appear.
How can I avoid Using where; Using filesort.
Reply to discussion (solution)
Userid is the primary key. why do I need group by userid ??
The primary key must be unique, and the group can be used.
Userid is the primary key. why do I need group by userid ??
The primary key must be unique, and the group can be used.
Thank you! After removal, only Using where is available.
Then, how can I obtain the preceding query result by using inner join or left join.
What does the query result mean?
Inner join and left join have different result sets and cannot be used.OrTied up
What does the query result mean?
Inner join and left join have different result sets and cannot be used.OrTied up
Change the mysql subquery statement to inner join or left join.
Only then can you find that you are querying the same table
Is it okay for the self-connection?
Only then can you find that you are querying the same table
Is it okay for the self-connection?
It is the same table. could you tell me how to write a self-join table in this case? please give an example. I can't write it myself, so it's useless.
Let's look at it.
select userid from team_enewsmember a, team_enewsmember b where a.agentid=b.userid and b.agentid='2' and b.levelid>=1 and a.levelid>=1
Let's look at it.
select userid from team_enewsmember a, team_enewsmember b where a.agentid=b.userid and b.agentid='2' and b.levelid>=1 and a.levelid>=1
Select a. userid from team_enewsmember as a, team_enewsmember as B where a. userid = B. userid and B. agentid = '2' and B. levelid> = 1
Select
A.Userid. thank you! In this way, how can we query level 3 nodes and level 4 nodes? does level 3 use self-join as a subquery?
The first node of 2 3
The first node of 3 4,
The first node of 4 5
Are you querying cascading data? Use recursion or modify the data structure.
If only four levels are fixed
Are you querying cascading data? Use recursion or modify the data structure.
If only four levels are fixed
Yes, you need to query other data related to each level of userid based on the detected userid. But that's okay. thank you!