Optimizar consulta

[granbosteron]

Hola gente, como andan?
Bueno tengo un SELECT a la base de datos, que el mismo lleva mucho tiempo de consulta, queria compartirlo con ustedes y ver si hay alguna manera de optimizarlo para que sea mas rapido, ya que segun como este configurado el servidor, puede tirarme un error por sobrepasar el limite de espera.

Tengo 3 Tablas:

ORDENES o (aqui guardo los datos del cliente y de la orden)
ORDENES TOTAL ot (aqui guardo el precio total de la orden)
ORDENES HISTORIAL oh (aqui fecha a fecha el historial de la orden, o sea cuando fue realizada la orden, a media que la orden se apruebe o sufra otras modificaciones, se ira guardando una fecha para la modificacion realizada)

Código PHP:

  SELECT  
  DISTINCT(o.orden_id),  
  o.cliente_id,  
  o.cliente_nombre,  
  o.cliente_apellido,  
  ot.total_precio,  
  COUNT(DISTINCT(oc.orden_id)) AS count_orden,  
  MAX(oh.fecha_inicial) AS fecha    
   
  FROM ordenes o    
  LEFT JOIN ordenes_total ot ON ( o.orden_id = ot.orden_id )  
  LEFT JOIN ordenes oc ON (o.cliente_id=oc.cliente_id)  
  LEFT JOIN ordenes_historial oh ON (o.orden_id=oh.orden_id)  
 
  GROUP BY o.orden_id  
   

Eh notado que al quitar el COUNT y el MAX de la fecha mayor de la orden, la consulta es mucho mas rapido. Explico que el COUNT lo utilizo para saber si ese cliente tiene mas ordenes realizadas, y el MAX es para buscar la fecha mas reciente de la tabla de historial de fechas.

Se me ocurrieron 2 soluciones:
1. Para el COUNT guardar en la tabla de CLIENTES, un campo para la cantidad de ordenes que tiene ese cliente e ir incrementando en 1 cada vez que vuelva a hacer un pedido.

2. Para el max de la fecha, tener dos campos en la tabla ORDEN, uno que sea fecha_inicial (para cuando realizo la orden) y el otro campo que sea fecha_modificacion (para guardar la fecha de la ultima modificacion). En este punto aclaro que la tabla ORDENES_HISTORIAL la tengo porque ahi voy guardando cada modificacion que se realiza en la orden, desde que la hace hasta cuando va cambiando de estado, lo hice asi porque ante cada modificacion se guardan textos que explican el porque de la modificaion y el cliente puede consultarlos en su panel de usuario. Es por eso que necesito el historial de las fechas.

Espero se entienda y puedan darme una mano para que la consulta sea mas rapida.

saludos

[quimfv]

Hola,

En la descripción de tu modelo de datos me falta alguna tabla y me sobra otra.

La tabla Clientes, dices que los datos del cliente los tienes junto con los de la orden, esto seria un error de diseño ya que te obliga a repetir los datos del cliente para cada orden que este haga, mas adelante en tu post hablas de una tabla clientes....pero en el query que nos pones no lausas...?!?!

Por otro lado tienes una tabla

ORDENES TOTAL ot (aqui guardo el precio total de la orden)

la que sugiere que deberia existir una ordenesDetalle con los datos que conforman ese total.

En principio la tabla total no haria falta puesto que el total puedes calcularlo a partir del detalle.

Debes seguir el princio de guardar totdos los datos unicos de una orden en la tabla Ordenes (la fecha de alta o inicial és un dato unico) y aquellos datos que no sean unicos (fechas de modificación, detalle del pedido...) en otra tabla.

Por tanto vamos a clarificar

ORDENES
idOrden
idCliente
fechaAlta
...
(aqui se guardan los datos de la orden, incluida fecha de alta o creació, otros campos fechaBaja... el total yo no lo pondria)

CLIENTES
idCliente
nombre
apellido
....

ORDENES HISTORIAL
idOrdenHist
idOrden
fecha
....
(aqui fecha a fecha el historial de la orden, o sea cuando fue realizada la orden, a media que la orden se apruebe o sufra otras modificaciones, se ira guardando una fecha para la modificacion realizada)

ORDENES_DETALLE
idOdernesDetalle
idOrden
descripcion
precio
...

(guardará los detalles de la orden, en especial los valores que deben formar el total de la orden, precio....)

Código sql:

SELECT COUNT(*) AS nordenes
FROM ordenes o1 
WHERE o1.idcliente=X
 

esto nos da el numero de ordenes del cliente con id=X

Código sql:

SELECT SUM(od.precio) AS total
FROM OrdenesDetalle od
WHERE od.idOrden=Y
 

esto nos da el total de la orden con id=Y


Luego

Código sql:

SELECT 
  o.orden_id, 
  o.cliente_id, 
  c.nombre, 
  c.apellido, 
  (SELECT SUM(od.precio) AS total
          FROM OrdenesDetalle od
          WHERE od.idOrden=o.orden_id) AS total, 
  (SELECT COUNT(*) AS nordenes
              FROM ordenes o1 
                    WHERE o1.idcliente=o.cliente_id) AS count_orden, 
  o.fechaAlta AS fecha
FROM Ordenes o
            INNER JOIN clientes c
                        ON o.idCliente=c.idCliente
 

esto nos da una lista de ordenes orden_id, Cliente_id, Nombre, Apellido, total, count_orden, fecha que creo que es lo que buscas...

Quim

[granbosteron]

Gracias Quim ... voy a probar y luego comento que paso.
Por cierto, obvie algunas tablas, y fui especificamente al problema de consulta que tengo. La tabla clientes y detalle de orden, ya existen. Vuelvo a grabar los datos del cliente en la orden, ya que puede ir modificandolos, pero deben quedar sus datos grabados en cada orden, segun como los habia completado en ese momento, es una especie de historial de ordenes tambien, se entiende?

bueno muchas gracias...
saludos

[quimfv]

Se entiende, pero no es muy correcto puesto que o la modificacion de los datos del cliente solo son para esa orden o los modificas directamente en la tabla clientes... pero claro tu conoces tus datos....

En cuanto a la tabla totl si sigues queriendo calcular el total no lo guardes en una tabla aparte guardalo en la tabla ordenes.

Quim