Consulta Demora mas de 10 minutos

[EduardoSk]

Hola tengo un query que tarda mas de 10 minutos y termina expirando del PHP

La tabla es la siguiente y tiene 49000 registros

Código MySQL:

CREATE TABLE IF NOT EXISTS `os_dispatch_closed` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `EnterpriseDocumentId` text,
  `SourceSystem` text,
  `BusinessProcess` text,
  `ObjectChangeActionType` text,
  `IntegrationCreationDateTime` datetime DEFAULT NULL,
  `Americas` text,
  `EMEA` text,
  `APG` text,
  `SenderId` text,
  `SenderName` text,
  `OriginalPartnerId` text,
  `OriginalPartnerName` text,
  `PurposeCode` text,
  `SPAccount` text,
  `ActivityDescription` text,
  `ActivityType` text,
  `AdditionalInformation` text,
  `BeginningTime` datetime DEFAULT NULL,
  `CallNumber` text,
  `CannedMessages` text,
  `DiagnosticCode` text,
  `Instructions` text,
  `RecommendedType` text,
  `ResolutionTime` datetime DEFAULT NULL,
  `ServiceLevelAgreementConfirmation` text,
  `ServiceRequestCommittedDate` text,
  `ShipToRequest` text,
  `WorkOrderNumber` text,
  `WorkOrderRequestedServiceDate` datetime DEFAULT NULL,
  `Severity` text,
  `ActivityCreateDateTime` datetime DEFAULT NULL,
  `Other` text,
  `SpecialTerms` text,
  `VersionOfWorkEffortIdentifier` text,
  `ReliefDate` datetime DEFAULT NULL,
  `ProductName` text,
  `DeviceTagCustomer` text,
  `DeviceTagDellService` text,
  `DeviceTagOem` text,
  `DeviceTagPpid` text,
  `DeviceTagServiceProvider` text,
  `AssetTag` text,
  `SerialNumber` text,
  `ProductIdentifier` text,
  `Model` text,
  `Type` text,
  `Reading` text,
  `AccountName` text,
  `AddressLineOne` text,
  `AddressLineTwo` text,
  `AddressLineThree` text,
  `City` text,
  `Code` text,
  `ContactName` text,
  `Country` text,
  `County` text,
  `FirstName` text,
  `LastName` text,
  `Name` text,
  `PostalCode` text,
  `Province` text,
  `PhysicalLocation1` text,
  `PhysicalLocation2` text,
  `PhysicalLocation3` text,
  `State` text,
  `StateProvince` text,
  `CustomerPhone` text,
  `ShipToContactTelephone` text,
  `MarketingName` text,
  `TLIPartNumber` text,
  `Quantity` text,
  `MachineTypeModelNumber` text,
  `ActivityLineNumber` text,
  `id_prestador` int(11) DEFAULT '0',
  `observaciones` text NOT NULL,
  `id_estado` int(11) NOT NULL DEFAULT '1',
  `id_activitystatus` int(11) NOT NULL DEFAULT '0',
  `FileID` text NOT NULL,
  `fecha_registro` datetime NOT NULL,
  PRIMARY KEY (`id`)
);
 

y el query que estoy haciendo es este

Código MySQL:

select 
    f.id,  
    DATE_FORMAT(f.BeginningTime,'%d-%m-%Y %H:%i:%s') as fechacreacionsrv, 
    f.CallNumber as servicio, 
    f.WorkOrderNumber as nrocaso, 
    f.ResolutionTime as fecharta, 
    f.AdditionalInformation as sla, 
    f.City as localidad, 
    f.SerialNumber as nroserie, 
    f.Model as modelo, 
    modelos.model AS modeloReal, 
    AccountName as ctaem presa, 
    activitystatus.descripcion as estadolmk, 
    estado,razonsocial, 
    f.observaciones 
from  
    modelos, 
    estados_os, 
    prestadores, 
    activitystatus, 
    (SELECT CallNumber, max(f echa_registro) as fechaupd  
    FROM os_dispatch_closed by CallNumber) as x  
    inner join os_dispatch_closed as f on f.CallNumber= x.CallNumber and f.fecha_registro= x.fechaupd 
WHERE  
    f.id_a ctivitystatus=activitystatus.id  
    and f.id_estado=estados_os.id  
    and f.id_prestador=prestadores.id  
    and REPLACE(f.model,';','')=modelos.machinetype  
    and f.id_acti vitystatus not like 82 
ORDER BY fecha_registro DESC;
 

como puedo optimizarlo?