consulta demasiado lenta por el order by

[lexus]

Hola a todos

uso mysql 4.1
y estoy haciendo una consulta sobre mas o menos 800mil registros

y es rapida casi instantanea pero cuando le coloco el order by se demora mas de 1 minuto en cargar
que otra manera hay de hacerla o como puedo optimizar esta consultapar ake sea mas rapida?



SELECT num_factura, sum(total_conIVA) as total, count(distinct(num_factura)) as cant FROM pedidos WHERE estado_pedido = 1 group by id_contacto order by cant desc

[DooBie]

Creo que añadiendole el estado INDEX al campo id_contacto desdephpmyadmin (o similar) a la tabla, creo que ayudaras en las ordenaciones.

OJO, no estoy 100% seguro! pero es reversible :)

[lexus]

ya tiene id_contacto como index.. y hasta ahi carga rapido, pero cuando le coloco el order by cant desc ahi es donde se vuelve exageradamente lento. ya ke ordenar 800ml registros es demaciado. y como cant es un conteo no sabria como poner el index ahi..
ke hago en este caso?

[huesos52]

Pregunta:
Es una tabla que tiende a crecer, o son 800000 registros estaticos?

si es así, podrías buscar la forma de vaciar los datos a otra tabla con el orden requerido y reestablecerlos ordenados. De esta forma, no necesitarías hacer uso de order by.

La otra cuestión es:
Realmente es necesario hacer un select tan grande?
vas a hacer uso de 800000 registros en una consulta?
Necesitas esta info para hacer calculos?

[lexus]

pues realmente la consulta seria asi

SELECT num_factura, sum(total_conIVA) as total, count(distinct(num_factura)) as cant FROM pedidos WHERE estado_pedido = 1 group by id_contacto order by cant desc limit 0,30

porke necesito sacar el top 30 de los mejores clients es decir los ke mas facturas tengan.

por eso tengo ke recorrer toda la tabla..
y mi tabla no es estatica cambia constantemente se incrementa el numero de regsitrsos porke ahi se meten las ventas diariamente..

como podria hacer?

[lexus]

que otra opcion podria usar en mi caso para que sea mas rapida? un TOP, un MAX o sera que la estoy haciendo mal o ke

[Rro_237]

No has probado con una paginacion?

[lexus]

si uso limit 0,30
pero eso no sirve de nada ya ke igual tiene ke recorrer todos los registros para poder hacer el conteo de facturas y poder sacar el top 30 del qeu tenga mas facturas ordenado descendientemente.

con paginacion o sin paginacion es igual de lento.

[DooBie]

Prueba a mandar esta sentencia antes de hacer la consulta:
SET sort_buffer_size=100000;

Con esto modificas el buffer a 100Kb (por defecto creo que está en 32Kb) con lo que en teoria se incrementa la rapidez.

Espero te sirva, y nos cuentas :)

[lexus]

ya lo hice y no mejoro al parecer kedo un poco mas lento que antes..
es la unica forma de hacer la consulta?

[huesos52]

Mira este articulo
http://www.tufuncion.com/optimizar_mysql

lastimosamente, no manejo cantidades de datos grandes para realizar las pruebas allí descritas.

saludos

[jurena]

Del tema no sé nada, Lexus, eso ya te lo anticipo, pero yo haría algunas pruebas
1) lo primero quitaría num_factura del select. No tiene mucho sentido traerte un num_factura si lo que sacas es un count… y un sum de un group by.

SELECT sum(total_conIVA) as total, count(distinct(num_factura)) as cant FROM pedidos WHERE estado_pedido = 1 group by id_contacto order by cant desc

2) Puedes cambiar el orden de los elementos del select para ver lo que ocurre
SELECT count(distinct(num_factura)) as cant, sum(total_conIVA) as total FROM pedidos WHERE estado_pedido = 1 group by id_contacto order by cant desc

3) Si consideras que el mejor cliente es el que más facturas distintas genera, quita el total, y esta sería casi mi preferida. No tiene sentido ordenar por cant si realmente no se dirime por cant, sino que también interviene el sum. Aquí no parece el caso. Quítalo.

SELECT count(distinct(num_factura)) as cant FROM pedidos WHERE estado_pedido = 1 group by id_contacto order by cant desc

4) Yo incluso probaría una subconsulta para ordenar, aunque seguro que es más lenta.

5) O trataría de limitar con el having. Puede que también sea más lenta, no lo sé. Imagina que un número de facturas medio es 5 y los que más tienen superan en cualquier caso las 20
SELECT count(distinct(num_factura)) as cant FROM pedidos WHERE estado_pedido = 1 group by id_contacto having cant > 20 order by cant desc

Con eso ya habrías eliminado a muchos en el order. Haz pruebas y dinos, pues estamos deseosos de saber los resultados (yo desde luego que sí).

Saludos a todos

[lexus]

antes que nada muchas gracias.
si voya leer con calma tus comentarios y a probar uno a uno..
pero si te respondo una
uso el sum(total_coniva) porque necesito mostrar en pantalla tanto el valor total de las ventas como la cantidad de veces qeue el cliente compra..
y pues mi cliente decidio ke es mas importante para el la cantidad de veces qeu compra que el valor total.
aunke debe aparecer en pantalla ambos valores

por otro lado hago un distinct(num_factura) y luego el count..
porque
es la tabla de ventas y se repite el num_factura varias veces (numero de productos vendidos)

es decir
num_factura
123
123
123
145
145
156
como veras la factura 123 se repite 3 veces pero se cuenta como si fuera una sola venta en este caso , la factura 145 se repite 2 veces pero se cuenta como 1 sola venta y asi suscesivamente.
por eso uso el distinct y el count. nose si este mal aplicado.

[jurena]

Está bien explicado y lo entiendo, no te preocupes, pero haz la prueba sin ese SUM y con el order by para ver si el retardo es el mismo que tienes ahora. Si fuera distinto, podríamos probar alguna otra cosa.

[lexus]

intente cambiando el orden colocando primero el count y luego el sum. y no mejoro mucho,
intente quitando el sum y dejandolo asi solo con el count
pero sigue exageradamente lento tambien..

SELECT count(distinct(num_factura)) as cant
FROM pedidos
WHERE tipo_documento = 1 AND anulada = 0 AND estado_pedido = 1
group by id_contacto
order by cant desc
limit 0,30

[jurena]

¿Y qué pasa si haces esto?
SELECT t1.idcont, count(distinct(t1.fac)) cant FROM (SELECT num_factura as fac, id_contacto idcont FROM pedidos WHERE (tip_documento = 1 AND anulada = 0 AND estado_pedido= 1))t1 GROUP BY t1.idcont ORDER BY cant DESC limit 0,30

[lexus]

me salio un error me dice "every derived table must have its own alias"

[jurena]

No sé, prueba así:
SELECT t1.idcont, count(distinct t1.fac) cant FROM (SELECT num_factura fac, id_contacto idcont FROM pedidos WHERE tip_documento = 1 AND anulada = 0 AND estado_pedido= 1)t1 GROUP BY t1.idcont ORDER BY cant DESC limit 30

[lexus]

hola..
gracias por la colaboracion..
ya mejoro un poco la velocidad
aunke estoy tatando de crear otra tabla con una informacion mas resumida par ke sea mas veloz..
muchas gracias por todo.

[aldo1982]

perdon el reabrir el post, pero tngo el miisimo problema :S alguna solution al repecto ?


salu2

[gnzsoloyo]

¿Y no probaste las propuestas de este trhread?
Al menos eso deberías intentar...