Optimizing SQL statement

Question

I am trying to run a sql query that is taking a lot of time on mysql. The table is very big (over 160000 rows). I have below table structure

id | clientID | type | price | code | created_at

Desired output is:

ldate | Cid | Total | Total2

I am currently using this query:

select max(created_at) as ldate, clientID as Cid, 
  (SELECT SUM(price) as total from invoice 
   where clientID = Cid and type = 9  and code <> 0) as total,
  (SELECT SUM(price) as total from invoice 
   where clientID = Cid and type = 9 ) as total2 
from invoice 
group by clientID 
having max(created_at) < '2019-09-01'

Is there a way to optimize this query to run faster or is the issue only related to the large number of rows.

Any suggestions?

Answer 1

If I correctly understand the intent of your query, you could use conditional aggregation rather than scalar subqueries, so the table is scanned only once:

select 
    max(created_at) as ldate, 
    clientid as cid, 
    sum(case when type = 9 and code <> 0 then price else 0 end) as total,
    sum(case when type = 9 then price else 0 end) as total2
from invoice 
group by clientid 
having max(created_at) < '2019-09-01'