(No need for duplicating the responses to Peter and me.)
Select ...
FROM tableaprerollup as ip
INNER JOIN tableb as p on ip.ProductId = p.Id
LEFT JOIN tl as l on ip.Id = l.InstalledProductId
where ip.ObligationType IN ('L')
GROUP BY ip.BusinessUnitId, ip.CustomerId, p.ShortNameId,
LeaseContractNumber, l.Lease_Cust_Id ;
You are GROUPing BY 5 things, but have many more non-aggregates in the SELECT; this could lead to unexpected results.
What table is LeaseContractNumber in? Please prefix all column names with their table aliases.
Why "LEFT"? It seems like don't really care if tl is needed.
Please provide the EXPLAIN SELECT ... I'll guess that it it works something like:
1. Collect all the rows WHERE ip.ObligationType IN ('L'). If there is not an index starting with ObligationType (and maybe if there is), that will be a "table scan".
2. For each row there, reach into tableb for one or more rows. This may involve reading the entire table, but in a random order.
3. For each row, reach into tl for zero or more rows. This may involve reading the entire table, but in a random order.
4. Write to tmp table.
5. Sort (for GROUP BY)
6. Deliver results.
Since I/O is likely to be the slowest part of the process, we need to look at when I/O happens.
1. Reading all of `ip` is relatively simple and efficient.
2. Reading all of tableb randomly is not too bad if there is room to cache all of it. But it it is too big to be cache, then the cache with thrash -- bumping out blocks to read (or reread) other blocks.
3. Ditto.
4,5,6. etc.
That is, steps 2 and 3 are likely to be the worst, and are likely to slow down dramatically after the table gets bigger than cache. Before it is too big, each block is read once. After it is really big, it will grow worse until, at the extreme, it will do a disk read for each _record_, not each _block_. As a rule of thumb, a block can hold 100 records.
So, not really "exponential", but potentially 100x slower.
This size of the table versus the cache size probably also plays an "exponential" (or other) role in slowdown.
Suggest you time each statement to see if I focused on the slow one.