Joined Min/Max and 'Having'

More a programming issue rather than a codeigniter issue. Got a bit of an issue with using 'Having' and joined tables. It works but its horrendously slow, looking for any input that may speed it up.

Code:

       $date = date("d-m-Y", $date);

       $from_date = strtotime($date . " 00:00:00");
       $to_date = strtotime($date . " 23:59:59");

       $this->db->select('*');
       $this->db->select('rental.rental_index as rental_index');
       $this->db->select('collect_branch.branch_name as collection_branch');
       $this->db->select('return_branch.branch_name as return_branch');
       $this->db->select('vehicle.*');
       $this->db->select('vehicle_group.*');
       $this->db->select('MIN(rental_date_time.collection_date_time) as rental_from_date');
       $this->db->select('MAX(rental_date_time.return_date_time) as rental_to_date');
       $this->db->join('rental_date_time', 'rental_date_time.rental_index = rental.rental_index', 'LEFT');
       $this->db->join('hirer_rental', 'rental.rental_index = hirer_rental.rental_index', 'LEFT');
       $this->db->join('hirer', 'hirer_rental.hirer_index = hirer.hirer_index', 'LEFT');
       $this->db->join('address', 'hirer_rental.address_index = address.address_index', 'LEFT');
       $this->db->join('branch as collect_branch', 'rental.collection_branch_index = collect_branch.branch_index', 'LEFT');
       $this->db->join('branch as return_branch', 'rental.return_branch_index = return_branch.branch_index', 'LEFT');
       $this->db->join('vehicle', 'rental.vehicle_index = vehicle.vehicle_index', 'LEFT');
       $this->db->join('vehicle_group', 'vehicle.vehicle_group_index = vehicle_group.vehicle_group_index', 'LEFT');

       if($branch_index != '0')
       {
           $this->db->where('collection_branch_index', $branch_index);
       }

       $this->db->where('rental_status != ', 'Deleted');
       $this->db->where('rental_status != ', 'Cancelled');
       $this->db->where('rental_status != ', 'Off Road');
       $this->db->having('rental_from_date > ', $from_date);
       $this->db->having('rental_from_date < ', $to_date);
       $this->db->order_by('rental_from_date', 'ASC');
       $this->db->group_by('rental.rental_index');
       return $this->db->get('rental')->result();

Everything works and it does return the correct results by as I've said it is very slow. If I remove the date range 'Having' then it works a lot better but obviously this part is necessary. Any help would be greatly appreciated.

More a programming issue rather than a codeigniter issue. Got a bit of an issue with using 'Having' and joined tables. It works but its horrendously slow, looking for any input that may speed it up.

Don;t put any tables into the min/max aggregation that you don't need there. In an aggregation (GROUP BY) query, every field returned must either be part of the GROUP BY or an aggregation field so if you include 5 lines of address, the customer name, favorite color etc.. then these must all be part of the GROUP BY which will cause it to be slow.

I would do this as a 2 part query.
The first part just gets the min/max rental records that you are interested in - you will need to include some sort of key back into the source tables if you need to get back at the hire reference. The second part would then link to the hirer, address, card payment etc.

I don't know if you can do this with the active record pattern, you might need to hand write the full query and than pass it as a complete SQL statement