MySQL Forums
Forum List  »  Performance

Re: COUNT with GROUP BY, JOIN and SUBQUERY very slow
Posted by: Øystein Grøvlen
Date: March 02, 2015 02:05AM


A sub-query in a FROM clause (aka derived table) will be materialized in a temporary table. With your long list of columns in the SELECT list, each row in this table will be pretty big. This seems unnecessary since you are not using the columns for anything, just counting the number of rows.

Also, AFAICT, the effect of the GROUP BY combined with COUNT is to give you the number of distinct id's in the query. It seems you can drop the outer-most SELECT, and the GROUP BY, and just do "SELECT COUNT(DISTINCT ..."

In fact, since you actually do not use any of the columns of the other tables,
and you make sure there is only one match per row of the outer table, the LEFT JOIN operations do not add anything to the query and might as well be dropped.

In other words, AFAICT, your complex query is equivalent to

Something tells me that is probably not what you are trying to achieve ...

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

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.