CODEWITHSUNDEEP
mysqlsql
RussellB
RussellB

Reputation: 358

Better optimized SELECT SQL query for 50,000+ records

I have a query which works great for 1000 records or less but now I need to optimize it for 50,000+ records and when I run it on that it just stalls...

Here is my code:

SELECT  
b1.account_num,b1.effective_date as ed1,b1.amount as am1,
b2.effective_date as ed2,b2.amount as am2
FROM bill b1
left join bill b2 on (b1.account_num=b2.account_num)
where b1.effective_date = (select max(effective_date) from bill where account_num = b1.account_num)
and (b2.effective_date = (select max(effective_date) from bill where account_num = b1.account_num and effective_date < (select max(effective_date) from bill where account_num = b1.account_num)) or b2.effective_date is null)
ORDER BY b1.effective_date DESC

My objective is to get the latest two effective dates and amounts from one table with many records.

Upvotes: 1

Views: 789

Answers (2)

radar
radar

Reputation: 13425

In mysql , window analytic function like row_number is not there, so we can simulate the same using variables.

The good thing is, the table is scanned only once with this approach.

A row_number is assigned to each partition which is divided based on ( account number, effective date ) and only 2 rows are selected from each partition.

select  account_num,
        max(case when row_number =1 then effective_date end) as ed1,
        max(case when row_number =1 then amount end) as am1,
        max(case when row_number =2 then effective_date end) as ed2,
        max(case when row_number =2 then amount end )as am2

from (
select account_num, effective_date, amount,
      @num := if(@prevacct= account_num , @num + 1, 1) as row_number,
      @prevacct := account_num as dummy
from bill, (select @num:=0, @prevacct := '' ) as var
order by account_num , effective_date desc
 )T
where row_number <=2
group by account_num

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Here is a working answer from your SQL-Fiddle baseline

First, the inner preQuery gets the max date per account. That is then joined to the bill table per account AND the effective date is less than the max already detected.

That is then joined to each respective bill for their amounts.

select
      FB1.account_num,
      FB1.effective_date as ed1,
      FB1.amount as am1,
      FB2.effective_date as ed2,
      FB2.amount as am2
   from
      ( select
              pq1.account_num,
              pq1.latestBill,
              max( b2.effective_date ) as secondLastBill
           from
              ( SELECT  
                      b1.account_num,
                      max( b1.effective_date ) latestBill
                   from
                      bill b1
                   group by
                      b1.account_num ) pq1
                 LEFT JOIN bill b2
                    on pq1.account_num = b2.account_num
                   AND b2.effective_date < pq1.latestBill 
            group by
               pq1.account_num ) Final
         JOIN Bill FB1
            on Final.Account_Num = FB1.Account_Num
            AND Final.LatestBill = FB1.Effective_Date

         LEFT JOIN Bill FB2
            on Final.Account_Num = FB2.Account_Num
            AND Final.secondLastBill = FB2.Effective_Date
   ORDER BY
      Final.latestBill DESC

Upvotes: 1

Related Questions