Re: COUNT with GROUP BY, JOIN and SUBQUERY very slow

[Rick James]

I'm going to assume the the SELECT COUNT(*) is a red herring, and the question is really about the big inner query.

One way to make the query faster is to avoid dragging all the t.* fields through all the tmp tables. You have

SELECT t.this, t.that, ...,
       `endereco_instalacao`.`id` AS `t1_c0`,...
    FROM radcliente AS t
    JOIN ...

Instead, do

SELECT r2.this, r2.that, ...  -- wait until now to pick up all these
       r1.t1_co,              -- from subquery
    FROM (
        SELECT t.id, 
                `endereco_instalacao`.`id` AS `t1_c0`,...
            FROM radcliente AS t
            JOIN ...
         ) AS r1
    JOIN radcliente AS r2  ON r2.id = r1.id

Another (minor) optimization is to turn the first LIMIT subquery into an EXISTS.

Do you need "LEFT"? If so, why?