optimize subquery with count and order by field-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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 .