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?

[gnzsoloyo]

Es difícil decir por dónde comenzar...
- ¿Por qué tantos campos TEXT? Ese tipo de campos sólo se usa para datos que requieran ese tipo de almacenamiento, y me cuesta creer que realmente todos esos campos sean necesarios así. ¿Por qué no VARCHAR?
- Hay iteraciones de atributos que podrían estar en otras tablas, y además campos que se infieren numéricos declarados como TEXT muestran una incorrecta definición de los tipos de dato.
- Tantas columnas, y el hecho de que se esté consultando sólo a una pequeña parte de ellas, me hace sospechar que esa tabla no está adecuadamente normalizada.
- El uso de JOIN implícitos (,) puede generar productos cartesianos. No podemos establecerlo porque sin conocer la estructura de las otras tablas es una suposición.
- La relación de tablas en el WHERE es de pésima performance. Está completamente desaconsejada y sería preferible declarar los INNER JOIN correspondientes en su lugar.
- El uso del REPLACE() permite inferir que existe un campo que parecer ser multivaluado, lo que es gravísimo para la performance de las consultas (sin olvidarse que es una violación flagrante del modelo relacional).
- LIKE se usa con cadenas de caracteres y no con números.
- Hay una subconsulta con un BY del que se desconoce el uso, pero en principio generaría un error de sintaxis.
...
Como dije: Es difícil decidir por donde empezar, pero yo empezaría por depurar el modelo de la tabla, porque esos campos TEXT no parecen razonables.

¿Podrías explicar estos puntos mencionados?