When i run the below query in MySQL 5.6 it shows 141000 records. but when i run this on MySQL 5.0 it fetches only 91 records

Question

Recently we had upgraded the MySQL from 5.0 to 5.6 in our test server. But our live server is still in MySQL 5.0 version.

When I run the below query in MySQL 5.6 it shows 141000 records, but when I run this on MySQL 5.0 it fetches only 91 records.

It takes less than 3 mins to retrieve in MySQL 5.6(141000 records) and 29 minutes in MySQL 5.0 (91 records).

I don't know what's the issue. No errors has been thrown.

In Live server, it'll take time to upgrade to 5.6. Sooner we will upgrade. But, I've to solve this problem to go live.

SELECT * FROM(SELECT IFNULL(clmem.accountid,opmem.accountid) accountid,clmem.clmem,opmem.opmem,IFNULL(clmem.valuationdate,opmem.valuationdate) valuationdate FROM
        (
            select accountid, valuationdate, clmem,@rownumber:=(CASE WHEN @accid= accountid THEN @rownumber+1 ELSE 1 END) AS rownumber,@accid:=accountid AS acc from 
            (SELECT accountid,valuationdate,SUM(mem) clmem FROM testtempmem GROUP BY accountid,valuationdate ) A
        ) clmem RIGHT JOIN
        (
            select accountid, valuationdate, opmem,@rownumber:=(CASE WHEN @accid= accountid THEN @rownumber+1 ELSE 1 END) AS rownumber,@accid:=accountid AS acc from 
            (SELECT accountid,valuationdate,SUM(mem) opmem FROM testtempmem GROUP BY accountid,valuationdate ) A
        ) opmem ON clmem.rownumber = opmem.rownumber+1 AND clmem.accountid = opmem.accountid
        WHERE clmem.accountid IS NOT NULL
        UNION 
        SELECT t.accountid, sum(t.mem) as clmem,0 as opmem, t.valuationdate  FROM testtempmem t 
            join (SELECT accountid,MIN(valuationdate) valuationdate FROM testtempmem GROUP BY accountid ) A 
            on t.accountid = a.accountid and t.valuationdate = a.valuationdate
            WHERE t.valuationdate > '2015-04-01' group by t.accountid, t.valuationdate 
    ) a ;