Mysql optimization problem Usingwhere; Usingfilesort

Source: Internet
Author: User
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.