SQL optimization (group by and max)

Question

Exist a chance to speedup the below sql query?

select 
    max(xtrid) as xtrid
  , jid
from jpltab jl
  inner join rb_cust u 
    on jl.custid = u.custid
where jl.tpe = 'Y'
  and jl.jid in (51, 52, 53, 54, 55)
  and u.org = 'INVCE'
group by jid
order by xtrid desc;

thanks

Since your `jl.jid` in your `Where` clause has consecutive numbers, change that to `Between 51 and 55` instead of the `IN (51, 52, 53, 54, 55)`. This should give you better performance. See here... https://stackoverflow.com/questions/3308280/is-there-a-performance-difference-between-between-and-in-with-mysql-or-in-sql-in — SS_DBA, May 24 '17 at 16:21
I would also move "and u.org = 'INVCE'" to your JOIN statement. You only want rb_cust to match jpltab for custid, but ONLY for rb_cust.org='INVCE'. Logically, that will make that initial JOIN a lot smaller, instead of JOINing for every custid and then filtering out for the org. The SQL Optimizer _may_ already handle this for an INNER JOIN, but I don't think it does. — Shawn, May 24 '17 at 16:36

score 1 · Answer 1 · answered May 24 '17 at 16:17

This is your query:

select jl.jid, max(xtrid) as xtrid
from jpltab jl inner join
     rb_cust u 
     on jl.custid = u.custid
where jl.tpe = 'Y' and
      jl.jid in (51, 52, 53, 54, 55) and
      u.org = 'INVCE'
group by jl.jid
order by xtrid desc;

I would start with indexes. What comes to mind is jpltab(tpe, jid, custid) and rb_cust(custid, org).

score -3 · Answer 2 · edited May 25 '17 at 01:45

-3

You can take out 'order by xtrid desc' as you're already selecting the max

edited May 25 '17 at 01:45

RBT

24,161
21
159
240

answered May 24 '17 at 16:15

heyhey

41
4

Yes... you don't need to order the values by group in order to select the max for each group. – heyhey May 24 '17 at 16:46

SQL optimization (group by and max)

2 Answers2