How to optimize the query having less than operator?

user_13816304 0
Viewed 73

I am using 1 million record to run this query, but never give me result even after 200 seconds, thus I kill the query. Below is the query

SELECT truncate((SELECT coalesce(sum(credit-debit),0) FROM trans_laga where uid = a.uid
    AND wallet_type = a.wallet_type AND status = 1 and freeze=0 and id<=a.id), 4)
    FROM `trans_laga` AS `a`
 WHERE a.status =1 AND a.wallet_type != 4 ORDER BY `a`.`id` desc
LIMIT 0, 50000;

Below is the explain output

enter image description here

Why this query running too slow, not even give me the result?

mysql
1 Answers
...
LEFT JOIN customer AS f ON a.customer_id=f.id
...
WHERE f.country = 'adminCountry'

This should be written as:

...
INNER JOIN customer AS f ON a.customer_id=f.id AND f.country = 'adminCountry'
...

Above will not make your query faster. For that you have to look at both lines in explain plan that have key=null

user_7240390
Related