Such as the question states it is possible to better method to write this:
SELECT clients_lists.*, COUNT(clients_lists_relationships.clientid) FROM clients_lists LEFT JOIN clients_lists_relationships ON clients_lists.listid = clients_lists_relationships.listid WHERE clients_lists.parentid = 1 GROUP BY clients_lists.listid;
Not necessarily. The query looks fine in my experience. You can test to make use of aliases for better readability:
SELECT c.*, COUNT(r.clientid) FROM clients_lists c LEFT JOIN clients_lists_relationships r ON c.listid = r.listid WHERE c.parentid = 1 GROUP BY c.listid;
However it will not really create a technical difference (not too I understand).
Also make certain that both
clients_lists_relationships.listid are of the identical datatype and length and therefore are indexed. This can certainly help your query's performance.