How to optimize query with many joins?

Left joins always return a row from the first table, but may return multiple rows if there are multiple matching rows. But because you are counting distinct visit rows, left joining to another table while counting distinct visits is the same as just counting the rows of visits. Thus the only joins that affect the result are inner joins, so you can remove all "completely" left joined tables without affecting the result.

What I mean by "completely" is that some left joined tables are effectively inner joined; the inner join to specialty requires the join to clients to succeed and thus also be an inner join, which in turn requires the join to clients_locations to succeed and thus also be an inner join.

Your query (as posted) can be reduced to:

Select Count(Distinct visits.id) As Count_idFrom visitsJoin clients_locations ON visits.client_location_id = clients_locations.idJoin clients ON clients_locations.client_id = clients.idJoin specialties ON clients.specialty_id = specialties.id

Removing all those unnecessary joins will however greatly improve the runtime of your query, not only because there are less joins to make but also because the resulting rowset size could be enormous when you consider that the size is the product of the matches in all the tables (not the sum.

For maximum performance, create a covering indexes on all id-and-fk columns:

create index visits_id_client_location_id on visits(id, client_location_id);create index clients_locations_id_client_id on clients_locations(id, client_id);create index clients_id_specialty_id on clients(id, specialty_id);

so index-only scans can be used where possible. I assume there are indexes on the PK columns.

You don't seem to have any (or much) intentional filtering. If you want to know the number of visits referred to in calls, I would propose:

select count(distinct c.visit_id)from calls c;

in order to optimize the whole process you can dynamically construct the pre-where SQL according to the filters you are going to apply. Like:

    // base select and left join     $preSQL = "Select  Count(Distinct visits.id) As Count_id From  visits ";    $preSQL .= "Left Join  clients_locations  ON visits.client_location_id = clients_locations.id ";    // filtering by province_id    $temp = $this->province_id;    if ($temp != null) {            $preSQL .= "Left Join  locations ON clients_locations.location_id = locations.id ";            $preSQL .= "Left Join  districts ON locations.district_id = districts.id ";            $preSQL .= "Left Join  provinces ON districts.province_id = provinces.id ";            $whereFilter = "provinces.id In ($temp) ";        }    $sql = $preSQL . "Where ".   $whereFilter;    // ...

If you are using multiple filters you can put all inner/left-join strings in an array and then after analysing the request, you can construct your $preSQL using the minimum of joins.