optimize subquery with count and order by field

Question

I am looking to optimize below query which has a subquery from relation table and has a order by on subquery count data. Please see the below query:

SELECT table1.*, 
       ( SELECT COUNT(*) 
         FROM table2 
         WHERE table2.user_id=table1.id 
           AND table2.deleted = 0) AS table2_total
FROM table1
WHERE table1.parent_id = 0 
ORDER BY table2_total DESC LIMIT 0, 50

This query works well but it stuck when table2 has more than 50K data. I have also tried to use left join instead of sub query but that is even more slower:

SELECT table1.*, 
       COUNT(DISTINCT table2.id) as table2_total 
FROM table1 
LEFT JOIN table2 ON table2.user_id=table1.id 
                AND table2.deleted = 0
WHERE table1.parent_id = 0 
ORDER BY table2_total DESC LIMIT 0, 50

table2 already has indexes on user_id and deleted column. Please see below table2 structure:

Is there any way to optimize this query in better way?

Answer 1

As written, it will go through the entirety of table1, and probe table2 that many times.

Add this composite index to table2: INDEX(user_id, deleted) and remove the INDEX(user_id) that you currently seem to have.

Answer 2

You can try to add index to the column table2.deleted And table1.parent_id. The index is going to impact the performance of the insert .