MySQL Forums
Forum List  »  Performance

Re: COUNT with GROUP BY, JOIN and SUBQUERY very slow
Posted by: Rick James
Date: March 02, 2015 06:13PM

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 (
                `endereco_instalacao`.`id` AS `t1_c0`,...
            FROM radcliente AS t
            JOIN ...
         ) AS r1
    JOIN radcliente AS r2  ON =

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

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

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.