CODEWITHSUNDEEP
mysqlsqlquery-optimization
user1479931
user1479931

Reputation: 244

SQL Query / Slow

I have the below SQL code, this is from a MySQL database. Now it gives me the results I expect, however the query is slow and I think I should speed this query up before going any further.

The table agentstatusinformation has:

PKEY (Primary Key), userid (integer), agentstate (integer)

The table axpuser contains the users name:

PKEY (Primary Key) <-- this is the key for userid, loginid (usersname)

select distinct (select loginid from axpuser where axpuser.pkey = age.userid),
   case
        when agentstate = 1 then 'Ready'
        when agentstate = 3 then 'Pause'
   end as state
from   agentstatusinformation age
where  (userid, pkey) in 
 (select userid, max(pkey) from agentstatusinformation group by userid)

I am sure this can be improved upon, but I cannot see the wood for the trees.

Many thanks.

Upvotes: 0

Views: 243

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The problem with your query are your nested selects. In particular, the subquery in the IN clause is problematic in MySQL. It gets called for every row filtered by the where clause.

The following fixes this:

select distinct (select loginid from axpuser where axpuser.pkey = age.userid),
   case
        when agentstate = 1 then 'Ready'
        when agentstate = 3 then 'Pause'
   end as state
from   agentstatusinformation age
where exists (select userid, max(pkey)
              from agentstatusinformation a2
              where a2.userid = age.userid
              group by userid
              having age.pkey = max(pkey))

You can make this run faster by creating an index on agenstatusinfromation(userid, pkey).

The nested select should not be causing a problem, as long as there is an index on axpuser.pkey. However, I think it is better form to put this in the FROM clause as a join:

select distinct axpuser.loginid,
   case
        when agentstate = 1 then 'Ready'
        when agentstate = 3 then 'Pause'
   end as state
from   agentstatusinformation age left outer join
       axpuser
       on axpuser.key = age.userid
where exists (select userid, max(pkey)
              from agentstatusinformation a2
              where a2.userid = age.userid
              group by userid
              having age.pkey = max(pkey)
             )

Upvotes: 1

David W
David W

Reputation: 10184

Not precisely certain this is what you want, but I think its close:

Select loginid, case when c.agentstate=1 Then 'Ready' 
                     when c.agentstate=3 then 'Pause' 
                end state
  from axpuser a
  join (select userid, max(pkey) pkey
          from agentstatusinformation 
          group by userid ) b 
     on a.userid=b.userid
   join agentstatusinformation c
    and b.pkey=c.pkey

This eliminates the subselect in the initial SELECT clause, and joins against the grouped stats information table. Hope this helps.

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

 select ax.loginid,
        case
        when age.agentstate = 1 then 'Ready'
        when age.agentstate = 3 then 'Pause'
        end as state 
from 
 agentstatusinformation age
 join 
 axpuser ax
 on age.userid = ax.userid and age.pkey=(select max(pkey) from agentstatusinformation group by userid)

Upvotes: 0

Related Questions