Hello All,
I am using a2billing version 1.8.1.I have around 250000 records in cc_receipt_item.When i open the receipt page from portal then it takes much time and i can not see the data in page even if waiting 4 to 5 hours. When i have checked from mysql processlist then it shows me there is 1 query is locked. The query which is used for get the data in receipt page and show me as locked in processlist is "SELECT SQL_CALC_FOUND_ROWS cc_receipt.id, username, cc_receipt.date,title,cc_receipt.status,total_price, cc_receipt.id FROM cc_receipt LEFT JOIN cc_card ON cc_card.id=id_card LEFT JOIN ( SELECT st1.id_receipt, SUM(st1.price) as total_price FROM cc_receipt_item AS st1 GROUP BY st1.id_receipt) as items ON items.id_receipt = cc_receipt.id ORDER BY id DESC LIMIT 0,10". If i keep 800 records in cc_receipt_item then it will show the data in receipt page from portal. Can you please help me how to resolve this problem? I have also tried with upgreading the a2billing version to 2.0.6.But still same issue faced. Does anybody know how to optimize this query or any other solution?. Thanks in advance.
|