how can i optimize this mysql statement, it is very slow

select _id as Id, concat_ws(' ',last_name,first_name) as name, (select title from posts_log where (_id = p.id_2)) as post, 
            (select count(_id) from profile_details where (profile_type = 'Client' and id_2 = p._id and ${dateRange('creation_date',fromDate,toDate)} and _status = 'Active')) as regs, 
            (select count(_id) from profile_details where (profile_type = 'Client' and id_2 = p._id and ${dateRange('closure_date',fromDate,toDate)} and _status = 'Exited')) as quits, 
            (select count(_id) from profile_details where (profile_type = 'Client' and id_2 = p._id and _status = 'Active')) as clients, 
            (select count(_id) from profile_details where (profile_type = 'Client' and id_2 = p._id and _status = 'Exited')) as closedClients, 
            (select count(_id) from profile_details where (profile_type = 'Center' and id_2 = p._id and _status = 'Active')) as centers, 
            (select count(_id) from profile_details where (profile_type = 'Center' and id_2 = p._id and _status = 'Exited')) as closedCenters, 
            (select ifnull(sum(loan_amount),0) as amount from loan_accounts la where (la._status = 'Active' and (date(la.disbursement_date) >= date('01-01-2022') and date(la.disbursement_date) <= date('01-31-2022')) and la.profile_id in (select _id from profile_details where (id_2 = p._id)))) as disbs, 
            (select ifnull(sum(amount),0) from transactions_log tl where (tl._status = 'Applied' and tl.transaction_type = 'Deposit' and (date(tl.entry_date) >= date('01-01-2022') and date(tl.entry_date) <= date('01-31-2022')) and tl.account_id in (select _id from savings_accounts where (profile_id in (select _id from profile_details where (id_2 = p._id)))))) as deposits, 
            (select ifnull(sum(abs(amount)),0) from transactions_log where (_status = 'Applied' and transaction_type = 'Withdrawal' and (date(tl.entry_date) >= date('01-01-2022') and date(tl.entry_date) <= date('01-31-2022')) and account_id in (select _id from savings_accounts where (profile_id in (select _id from profile_details where (id_2 = p._id)))))) as withdrawals, 
            (select ifnull(sum(amount),0) from transactions_log where (_status = 'Applied' and (transaction_type = 'Repayment' or transaction_type = 'Write Off') and (date(tl.entry_date) >= date('01-01-2022') and date(tl.entry_date) <= date('01-31-2022')) and account_id in (select _id from loan_accounts where (profile_id in (select _id from profile_details where (id_2 = p._id)))))) as repayments, 
            (select ifnull(sum(expected_repayment_amount),0) from schedules_log where (_status in ('Pending','Partially Paid','Arrears') and expected_repayment_amount > 0 and (date(tl.entry_date) >= date('01-01-2022') and date(tl.entry_date) <= date('01-31-2022')) and account_id in (select _id from loan_accounts where (profile_id in (select _id from profile_details where (id_2 = p._id)))))) as defaults, 
            (select ifnull(sum(abs(balance)),0) as amount from savings_accounts where (_status = 'Active' and profile_id in (select _id from profile_details where (id_2 = p._id)))) as savings, 
            (select ifnull(sum(abs(balance)),0) as amount from loan_accounts where (_status = 'Active' and profile_id in (select _id from profile_details where (id_2 = p._id)))) as loan, 
            (select ifnull(sum(expected_repayment_amount),0) as amount from schedules_log where (_status in ('Pending','Partially Paid','Arrears') and expected_repayment_amount > 0 and date(expected_repayment_date) < date(now()) and account_id in (select _id from loan_accounts where (profile_id in (select _id from profile_details where (id_2 = p._id)))))) as totalDefaults 
            from profile_details p where (p._id in (1042239621,1307205751,2458884621));

Answer

This query is slow because it is actually 16 x 3 = 48 queries. You have 16 SELECT correlated subqueries in your main SELECT query, and you’re looking up three values of p._id.

Your first step is to transform at least some of those subqueries into independent subqueries and JOIN them. (This is a vital skill for people who use SQL.)

I’ll show you how to refactor a couple of them.

First, the easy one — the post title. Just LEFT JOIN your posts_log table to your profile_details table ON the appropriate id values.

SELECT profile_details._id AS Id, 
       CONCAT(' ', p.last_name, p.first_name) AS name,
       posts_log.title AS post
  FROM profile_details p
  LEFT JOIN posts_log    ON posts_log._id = p._id2
 WHERE p.id_2 IN  (1042239621,1307205751,2458884621);

Next, let us write a subquery to retrieve your your regs column in your result set. This aggregating (GROUP BY) subquery generates the regs value for each value of _id_2 in the table.

                    SELECT id_2, COUNT (_id) AS regs
                    FROM profile_details
                    WHERE profile_type = 'Client'
                      AND _status = 'Active'
                      AND ${ dateRange('creation_date', fromDate, toDate) }
                    GROUP BY id_2

Then you LEFT JOIN that subquery in your main query, and SELECT the result.

SELECT profile_details._id AS Id, 
       CONCAT(' ', p.last_name, p.first_name) AS name,
       posts_log.title AS post,
       regs.regs
  FROM profile_details p
  LEFT JOIN posts_log    
            ON posts_log._id = p._id2
  LEFT JOIN (
                    SELECT id_2, COUNT (_id) AS regs
                    FROM profile_details
                    WHERE profile_type = 'Client'
                      AND _status = 'Active'
                      AND ${ dateRange('creation_date', fromDate, toDate) }
                    GROUP BY id_2
            ) regs
            ON regs.id_2 = p._id2
 WHERE p.id_2 IN  (1042239621,1307205751,2458884621);

That’s the pattern to use.

(I am confused about whether your _id values start with underscores or not. You didn’t show us your table definitions so it’s hard for me to get that right. Also, I assume the ${ date_range ... } stuff expands to a valid SQL date-range filter.)