Hi, Can some one explain me how to optimise this query?
select disticnt(csuser.id), csuser.loginName,csuser.displayName,loginSession.l astlogindatetime,loginSession.activesessions,usert ypes.csusertypeid from User csuser join userroleassign csuserroleassign on csuserroleassign.roleid=52 left outer join userloginsession csloginSession on csloginSession.userid=csuser.id left outer join userusertypeassign usertypes on usertypes.userid=csuser.id limit 15;
If I don't add that distcint keyword, query is returning results in 18 milli seconds; but if I add distcinct it did not return results in two miunutes and finally I have to stop the exection of the query.
Interestingly, if I say select distinct(user.id) from user; then it is pretty quick and returning in 15 milliseconds.
Sorry for asking such a basic question but I'm not that good at SQL.
Did you miss a join condition on the userroleassign csuserroleassign table? Your example shows the only limiting criteria on that table is the role id being a 52. Without join criteria to your other tables this would create a cartesian product, which could explain the length of time it takes to sort all rows. Also, when you say data is returned in 18 milliseconds without the distinct, is that all rows or just a first subset of the rows?