I have to get the country with the highest amount of clients from a data base. I have the following code on MySQL using nested selects:

select name as Country, max(cnt) as 'Amount of clients'
from (select countries.name, count(clients.id) as cnt from clients
inner join countries on clients.country = countries.id) as Results;

The code is working fine. But I wonder if it could be better in terms of time performance if instead of that I use ORDER BY, like for example:

select countries.name as Country, count(clients.id) as 'Amount of clients'
from clients inner join countries on clients.country = countries.id
order by 'Amount of clients' desc limit 1;

Could someone explain me if there is an important difference between them and if one of both is better or if it doesn't really matters. Thanks.

CodePudding user response：

Premature optimization is the root of all evil. If the query is used seldom and works quickly you shouldn't optimize it.

Having said that I personally would use the second option. Subqueries are harder to read, and should be avoided to make it easier for the next developer (that might very well be you). And they tend to get BAD performance issues if not done correctly.

Just note that "limit" is mysql specific, the query have to be rewritten if you want to run it on another sql server.