Drama con SUM( multiplicación) a la vez

[saggas]

Hola todos, estoy trabajando en una empresa que se dedica ala comercialización de productos, Tiene un sistema que trabaja en Linux con PHP y MySql..
Mi problema es que tengo que llenar una tabla temporal diariamente para realizar un informe de gestión. La consulta

Código PHP:

  select sum(db.CANTDESP*db.PRECUNIT),count(DISTINCT db.NUMRECOR) as cantidad_pedido1 from NOTDE_DB db  
join NOTV_DB tv on tv.NUMREG=db.NUMRECOR 
join ART_DB art on art.NREGUIST=db.NCODART 
join aclase6_codigo a on a.codigo=art.CODIGO 
where 
tv.FECHA between '2011-01-01 00:00:00' and '2011-01-25 23:59:59' and 
tv.CODVEND=85 and                                          
a.nombre='Juan Soto';  
   

La consulta sacaría la sumatoria de las ventas diarias de un vendedor y la cantidad de productos vendidos de un vendedor por un periodo, todo bien, el problema es cuando se ejecuta la consulta en Mysql se demora por un vendedor como 15 minutos existiria la manera de agilizarla ó realizarla de otra forma.

gracias

[gnzsoloyo]

Hay cosas realmente ineficientes en esa consulta:
- ¿Para qué consultar por el nombre del vendedor, si ya consultas por su código? Es más que redundante: Es innecesario. Y si la idea es poner el nombre en la consulta, bueno, no lo estás haciendo...
- JOIN no es exactamente igual a INNER JOIN. En tu caso es más eficiente usar INNER JOIN porque JOIN intenta primero hacer una junta entre campos con el mismo nombre, aunque tengan dominios distintos. Puede estar generando productos cartesianos.
- Preguntar por un campo DATETIME y contra valores DATETIME, cuando en realidad necesitas sólo la parte DATE, puede generar baja de performance. Exige al sistema leer más bytes innecesariamente.
- El orden de las tablas genera diferencias de resultado. Si te interesa la información de los vendedores, es mejor que la primera tabla sea la de lso vendedores. Si te interesa la de un solo vendedor, ni siquiera necesitas leer la tabla vendedores, si ya tienes su ID...

Hay si un detalle que no se termina de comprender. ¿Qué contiene exactamente la tabla "aclase6_codigo"?
Porque "ART_DB" parece ser la de artículos, "NOTV_DB" la de facturas o notas de venta, "NOTDE_DB" algo así como notas de débito, pero "aclase6_codigo" no se entiende.
Además, ¿Por qué aparece un campo "CODVEND" que parece ser el ID del vendedor, y luego usas el nombre para la búsqueda?

En esa parte, me parece que el problema mayor puede deberse a un diseño erróneo de tablas.

[saggas]

Hola gnzsoloyo gracias por los comentarios si en verdad estoy de acuerdo contigo sobre el diseño, es una mierda, pero es lo que hay.
Lo del nombre error mió tienes razón.
Lo del inner entendía que era al revés, que el ínter se usaba para tablas idénticas, lo cambio y pruebo mañana en la pega.
Pregunto por datetime porque me dijeron que si no preguntaba por el horario 23:59:59 no tomaría todos los registros de ese día.
Los join los uso para unir tablas,
NOTDE_DB esta el detalle de lo Facturado
NOTV_DB lo facturado
ART_DB los códigos de los productos.
aclase6_codigo solo contiene los códigos que pertenecen a un canal específico, o marca.
Gracias por las observaciones modifico mañana en la pega y publico resultado.

[gnzsoloyo]

Bueno, tip final, podemos ajustarla un poquitín y veamos:

Código MySQL:

SELECT
  SUM(db.CANTDESP * db.PRECUNIT) suma,
  COUNT(DISTINCT db.NUMRECOR) cantidad_pedido1
FROM
  NOTDE_DB db
  INNER JOIN
  NOTV_DB tv ON tv.NUMREG=db.NUMRECOR
  INNER JOIN
  ART_DB art ON art.NREGUIST=db.NCODART
  INNER JOIN aclase6_codigo a ON a.codigo = art.CODIGO
WHERE
  (DATE(tv.FECHA) BETWEEN '2011-01-01' AND '2011-01-25')
  AND
  tv.CODVEND=85;
 

Por otro lado, es muy probable que puedas mejorarlo mucho definiendo algunos índices en datos claves. Pruébalo.

[saggas]

Que lata mala se demora lo mismo alguna otras ideas con join separados a la misma tabla a mi definitibamente se me terminaron las ideas.

[gnzsoloyo]

¿Por qué no nos muestras lo que te devuelve el análisis de EXPLAIN para tu consulta?
Eso nos peude dar una mejor idea de dónde se está produciendo el problema.
Usa algún front-end (phpMyadmin, por ejemplo) y ejecuta la consulta con EXPLAIN. Luego postea la tabla de datos que resulte.
Eso nos dará idea de a qué nos enfrentamos.

[saggas]

Hola mira la consulta como la armo en local y corre bien

Código PHP:

  select sum(db.CANTDESP*db.PRECUNIT), count(DISTINCT db.NUMRECOR) as cantidad_pedido1  
from NOTDE_DB db  
inner join NOTV_DB tv on tv.NUMREG=db.NUMRECOR  
inner join ART_DB art on art.NREGUIST=db.NCODART join aclase6_codigo a on a.codigo=art.CODIGO where (DATE(tv.FECHA) between '2011-06-01 00:00:00' and '2011-06-28 23:59:59') and tv.CODVEND=84 and a.nombre='SP'  
   

estos datos se los pasos a un update denro de un ciclo for al mismo tiempo

Código PHP:

  'update pp_vendedor_venta_meta set pedido_mes=10409900.00000, cantidad_pedido=4 WHERE id_pp_forecast_vendedor=84 and fecha='2011-06-28' and clase6='SP  
   

la respuesta de la consulta

Código PHP:

  sum(db.CANTDESP*db.PRECUNIT)  cantidad_pedido1   
10409900.00000                                                 4  
   

Me trato de explicar la consulta me corre super bien y rapioda con pocos regsitros pero la tabla NOTDE tine como 5 registros, y las otras no bajan de los 1000 mi problema son los cruzes ò el calculo de la consulta, los cruzes por la cantidad de registros.
Por eso consultab si existia otra forma de realizar la consulta mà eficiente.
PD Tengo mi jefe respirandome enn el hombro me tiene chato.

[gnzsoloyo]

Te insisto: Usa el EXPLAIN y postea el resultado. Es lo mejor que puedes hacer en este punto.

Esta es tu consulta:

Código MySQL:

SELECT 
    SUM(db.CANTDESP*db.PRECUNIT), 
    COUNT(DISTINCT db.NUMRECOR) cantidad_pedido1 
FROM NOTDE_DB db 
    INNER JOIN NOTV_DB tv ON tv.NUMREG=db.NUMRECOR 
    INNER JOIN ART_DB art ON art.NREGUIST=db.NCODART 
    INNER JOIN aclase6_codigo a ON a.codigo=art.CODIGO 
WHERE 
    (DATE(tv.FECHA) between '2011-06-01' and '2011-06-28') 
    AND tv.CODVEND=84 
    AND a.nombre='SP'
 

Una cosa: Si estás poniendo ya la función DATE() como te lo dije, haz la comparación con las fechas sin horas. En este punto esa fuinción está suprimiendo las horas y sólo tomará en cuenta las fechas, ya no es necesario ese nivel de precisión

Toma la consulta y usa el EXPLAIN de las dos formas:

Código MySQL:

EXPLAIN
SELECT 
    SUM(db.CANTDESP*db.PRECUNIT), 
    COUNT(DISTINCT db.NUMRECOR) cantidad_pedido1 
FROM NOTDE_DB db 
    INNER JOIN NOTV_DB tv ON tv.NUMREG=db.NUMRECOR 
    INNER JOIN ART_DB art ON art.NREGUIST=db.NCODART 
    INNER JOIN aclase6_codigo a ON a.codigo=art.CODIGO 
WHERE 
    (DATE(tv.FECHA) between '2011-06-01' and '2011-06-28') 
    AND tv.CODVEND=84 
    AND a.nombre='SP'
 

Código MySQL:

EXPLAIN
SELECT 
    SUM(db.CANTDESP*db.PRECUNIT), 
    COUNT(DISTINCT db.NUMRECOR) cantidad_pedido1 
FROM NOTDE_DB db 
    INNER JOIN NOTV_DB tv ON tv.NUMREG=db.NUMRECOR 
    INNER JOIN ART_DB art ON art.NREGUIST=db.NCODART 
    INNER JOIN aclase6_codigo a ON a.codigo=art.CODIGO 
WHERE 
    (DATE(tv.FECHA) between '2011-06-01 00:00:00' and '2011-06-28 23:59:59') 
    AND tv.CODVEND=84 
    AND a.nombre='SP'
 

Luego copia la tabla resultado de esos EXPLAIN y posteanos lo que devuelven,.

[saggas]

Muchas gracias gnzsoloyo por la ayuda, con los inner joins mejoro el tiempo con la funcion date tambien, lo dejo asi ya que me pidiron que lo entregara

Código:

select sum(db.CANTDESP*db.PRECUNIT), count(DISTINCT db.NUMRECOR) as cantidad_pedido1 from NOTDE_DB db 
inner join NOTV_DB tv on tv.NUMREG=db.NUMRECOR
inner join ART_DB art on art.NREGUIST=db.NCODART
inner join aclase6_codigo a on a.codigo=art.CODIGO
where
(date(tv.FECHA) between '2011-06-01' and '2011-06-31') and
tv.CODVEND=84 and										 
a.nombre='SP';

gracias.

[gnzsoloyo]

Finalmente nos quedamos con la duda de qué es lo que devuelve el EXPLAIN de esa consulta.
¿Por qué no lo has posteado?
¿Lo hiciste? ¿Probaste el EXPLAIN?

[saggas]

No lo proble, no lo se utilizar

[gnzsoloyo]

TE lo puse en el post #8, escrito tal y como lo debes usar.
Simplemente tenías que copiar la consulta y usarla como está:

Código MySQL:

EXPLAIN
SELECT 
    SUM(db.CANTDESP*db.PRECUNIT), 
    COUNT(DISTINCT db.NUMRECOR) cantidad_pedido1 
FROM NOTDE_DB db 
    INNER JOIN NOTV_DB tv ON tv.NUMREG=db.NUMRECOR 
    INNER JOIN ART_DB art ON art.NREGUIST=db.NCODART 
    INNER JOIN aclase6_codigo a ON a.codigo=art.CODIGO 
WHERE 
    (DATE(tv.FECHA) BETWEEN '2011-06-01' AND '2011-06-28') 
    AND tv.CODVEND=84 
    AND a.nombre='SP'
 

[saggas]

Esto retorna

Código:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE db ALL NULL NULL NULL NULL 7   
1 SIMPLE tv ALL NULL NULL NULL NULL 7 Using where 
1 SIMPLE a ALL NULL NULL NULL NULL 765 Using where 
1 SIMPLE art eq_ref PRIMARY,CODIGO,NREGUIST PRIMARY 4 m_suite.db.NCODART 1 Using where

[gnzsoloyo]

En principio, lo que se ve en ese reporte es que aclase6_codigo es la tabla más ineficiente. Es probable que la consulta mejore un tanto si le creas un índice sobre el campo codigo.
Por otro lado, tal parece que los campos relacionados de las otras tablas no son en realidad FK, como si las tablas fuesen MyISAM y no InnoDB, por lo que en realidad no está usando índices para vincularlos y eso puede hacer la consulta algo lenta.

[saggas]

Gracias gnzsoloyo el reporte lo tomo mi jefe que el maneja la BD, yo no puedo realizar cambios,tampoco me da acceso a la BD real.
Yo creo que me estan jorobando pero == estoy de paso en este trabajo y la PR va ==, nunca habia trabajado en un lugar don de las tablas no tengan una buen estructura y menos indices.

gracias.