What you are doing as a correlated query on every wallet ID to get the corresponding debits and credits. It appears you are getting one record per wallet id. This is very busy. Having a join to the customer payments table on your criteria that is common (including the join per wallet id). Then, simplify the CASE as a SUM( case/when ) as respective debit / credit.
I don't know your underlying criteria of table columns, but I would even hedge to (and did) include NOT the CE_TYPE = 'IN' as that appears basis of a debit and you would not want to falsely count as part of a credit too. Again, dont know correlation of fields, trans_id, types.
Now, as stated, having individual indexes on individual fields will not help optimize this query. I would suggest the following indexes.
table index customer_wallet ( company_id, is_approved, currency, id, date_added ) customer_payment_options ( wallet_id, account_type, country_id )
SELECT cw.wallet_id, SUM( case when cpo.ce_type = 'IN' then cpo.payment_amount ELSE 0 end ) as Debit, SUM( case when NOT cpo.ce_type = 'IN' AND cpo.tran_id IS NOT NULL then cpo.payment_amount ELSE 0 end ) as Credit FROM customer_wallet cw JOIN customer_payment_options cpo ON cw.id = cpo.wallet_id AND cpo.real_account_type = 'HQ' AND cpo.source_country_id = '40' WHERE cw.company_id = '1' AND cw.currency = '40' AND cw.is_approved = '1' AND cw.date_added < '2016-03-30' GROUP BY cw.id
One additional comment. if your ID columns, Currency flag, country ID, approved are actually numeric values in the table structure, remove the quotes and let compare directly on the numeric value. Also, for your date_added. You had that based on DATE( date_added ). Doing a function on a column can not fully utilize the index. Since date() strips off any time portion of a date/time stamp column, and you are asking for all added less than Mar 30, then date added of March 29 @ 11:59:59pm is still less than Mar 30 at 12:00:00am, so no date conversion is required.
As commented by Ivan (below), if you want ALL Wallet IDs regardless of having any payments (debit or credit), then change from a join to a LEFT JOIN.