optimizar consulta con 2 tablas mysql

[seronellas]

Buenas, tengo problemas al ejecutar ciertas consultas sobre una BD que tengo y al tardar tanto en procesarlas siempre me salta error 500. Utiliza 2 tablas y varios filtros de busqueda:

SELECT *
FROM stock
WHERE stock.id_sucursal =$sucursal
AND id
IN (

SELECT DISTINCT id_stock
FROM new_log
WHERE fecha_alta >= '$fecha_ini'
AND fecha_alta <= '$fecha_fin'
AND operacion LIKE 'MOVIMIENTO STOCK'
AND id_sucursal_destino =$sucursal
)

Intuyo que no es muy eficiente dicha búsqueda. Investigando por internet lei algo sobre una explicación de EXPLAIN. Al ejecutarlo sobre ambas tablas (stock y new_log) me da esto:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stock ALL NULL NULL NULL NULL 8229

Como puedo optimizar dicha consulta??

Muchas gracias de antemano.

[Vun]

¿que indices tienes creados? Prueba creando tres indices secundarios en la tabla new_log, uno para fecha_alta, otro para operacion y otro para id_sucursal_destino

Y en la tabla stock por supuesto un indice primario con el campo id_stock

Prueba asi a ver si logramos que tarde menos...

[seronellas]

Iniciado por Vun

¿que indices tienes creados? Prueba creando tres indices secundarios en la tabla new_log, uno para fecha_alta, otro para operacion y otro para id_sucursal_destino

Y en la tabla stock por supuesto un indice primario con el campo id_stock

Prueba asi a ver si logramos que tarde menos...

Perdona mi ignorancia, pero como pasa en muchos casos mi area en informatica es redes pero de repente me han encargao de administrar una aplicacion en php y mysql, asi que estoy aprendiendo a base de golpes.

Podrías explicarme mas o menos como crear esos subíndices?

[Vun]

¿tienes en tu servidor instalado phpmyadmin? Es un script para gestionar tu base de datos, desde ahi se pueden crear indices y de todo sin tener mucho conocimiento de MYSQL. Echa un vistazo o preguntale a alguien encargado del servidor y me dices.

[seronellas]

Iniciado por Vun

¿tienes en tu servidor instalado phpmyadmin? Es un script para gestionar tu base de datos, desde ahi se pueden crear indices y de todo sin tener mucho conocimiento de MYSQL. Echa un vistazo o preguntale a alguien encargado del servidor y me dices.

Si, utilizo phpmyadmin, y ahora soy el nuevo "encargado" de administrar dicha base de datos.. (recorte de personal)

Una vez creados los indices secundarios tengo que programar algo mas en php o directamente mysql lo gestiona?

En la tabla stock no tengo ningun campo id_stock, este corresponde a new_log... En stock solo tengo id

[gnzsoloyo]

Los indices son gestionados internamente por MySQL. Pero si una consulta en particular puede mejorar con el uso de uno de ellos, debe ser escrita de modo tal que priorice ese índice sobre los demás. Esto se hace usando la cláusula FORCE INDEX nombreindiceen la invocación de la tabla.

Código MySQL:

SELECT a, b, c, d, e
FROM tabla FORCE INDEX indicedelatabla INNER JOIN otratabla
WHERE condiciones;
 

¿Cuándo hacerlo? Bueno, eso surge del análisis de la consulta que se realiza al usar EXPLAIN en la etapa de desarrollo.

Código MySQL:

EXPLAIN SELECT a, b, c, d, e
FROM tabla FORCE INDEX indicedelatabla INNER JOIN otratabla
WHERE condiciones;
 

La tabla resultado del EXPLAIN da una serie de informaciones acerca del funcionamiento de la sentencia usada.

[Vun]

¿Y bien? ¿creaste los indices? ¿te funciona mejor el script?

Por cierto, es mejor que tu consulta sea asi:

SELECT *
FROM stock
WHERE stock.id_sucursal =$sucursal
AND id
IN (

SELECT DISTINCT id_stock
FROM new_log
WHERE fecha_alta >= '$fecha_ini'
AND fecha_alta <= '$fecha_fin'
AND operacion LIKE 'MOVIMIENTO STOCK'
AND id_sucursal_destino =stock.id_sucursal
)


Con eso que te puse en rojo es cuando MYSQL empieza a relacionar unas tablas con otras, total en la consulta "base" ya tienes stock.id_sucursal =$sucursal , por lo que he sustituido $sucursal por stock.id_sucursal.

Lo ideal seria que hicieras un tutorial basico de MYSQL, porque meterse a usar subconsultas+IN+DISTINCT+LIKE para alguien nuevo te va a resultar algo frustrante.

Prueba eso que te puse y pon aqui el EXPLAIN igual que hiciste en el primer post.

[seronellas]

He hecho la modificacion en rojo y al ejecutar un EXPLAIN me sale esto:

Código:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	stock	ALL	NULL	NULL	NULL	NULL	8229	Using where
2	DEPENDENT SUBQUERY	new_log	ref	fecha_alta,operacion,id_sucursal_destino	id_sucursal_destino	4	smovilsur_db.stock.id_sucursal	800	Using where; Using temporary

Lo que no he probado aun es lo de FORCE INDEX...

[Vun]

Bueno, ese "Using temporary" son malas noticias, estamos forzando a MYSQL a crear una tabla extra temporal para tu consulta.

¿Que valores tienes en el campo operacion?

Crea un indice en la tabla stock para el campo id_sucursal

[seronellas]

Iniciado por Vun

Bueno, ese "Using temporary" son malas noticias, estamos forzando a MYSQL a crear una tabla extra temporal para tu consulta.

¿Que valores tienes en el campo operacion?

Crea un indice en la tabla stock para el campo id_sucursal

Supongo que ahi estara el problema. Al observar PROCESSES veo que se queda en "Copying to tmp table" y ahi es cuando se tira todo el rato procesando.

Los valores de operacion solo son 3: ALTA STOCK, MOVIMIENTO STOCK, ASIGNACION

[gnzsoloyo]

El problema no es tanto que genere una tabla temporal. El problema es que esa tabla tiene baja selectividad, lo que significa que es innecesariamente grande.
Trata de filtrar mejor o replantea todo de nuevo.

[seronellas]

Iniciado por gnzsoloyo

El problema no es tanto que genere una tabla temporal. El problema es que esa tabla tiene baja selectividad, lo que significa que es innecesariamente grande.
Trata de filtrar mejor o replantea todo de nuevo.

Grande en que sentido?

new_log solo tiene 16.800 registros y stock 8.200...

[gnzsoloyo]

new_log solo tiene 16.800 registros y stock 8.200...

Y con esas tablas, ún JOIN, por ejemplo, generaría una tabla temporal de 137.760.000 registros, por ejemplo (no es este caso, pero sucederia si no pusieses condiciones en el FROM).

Tu consulta es optimizable:

Código MySQL:

SELECT *
FROM stock S INNER JOIN new_log N ON N.id_sucursal_destino =S.id_sucursal
WHERE stock.id_sucursal =$sucursal
AND fecha_alta BETWEEN '$fecha_ini' AND fecha_alta <= '$fecha_fin'
AND operacion LIKE 'MOVIMIENTO STOCK';
 

Por otro lado, LIKE en ese contexto resultaría igual que usar "=", con lo qu eesto se podría escribir:

Código MySQL:

SELECT *
FROM stock S INNER JOIN new_log N ON N.id_sucursal_destino =S.id_sucursal
WHERE stock.id_sucursal =$sucursal
AND fecha_alta BETWEEN '$fecha_ini' AND fecha_alta <= '$fecha_fin'
AND operacion = 'MOVIMIENTO STOCK';
 

Esto último es porque no estás usando comodines en el texto buscado, con lo que sólo será verdadero si es igual.

[seronellas]

Pues he probado con esta consulta y no salta el timeout. Es como si fuese mostrando los resultados en tiempo real, los va sacando. El tema es que es un disparate el resultado que me da, mas de medio millon de registros. ME he fijado y duplica, como puedo quitarlo?

Código:

SELECT *
FROM stock  INNER JOIN new_log ON new_log.id_sucursal_destino =stock.id_sucursal
WHERE stock.id_sucursal =$sucursal
AND new_log.fecha_alta BETWEEN '$fecha_ini' AND '$fecha_fin'
AND new_log.operacion = 'MOVIMIENTO STOCK'

Se supone que con INNER JOIN me va a mostrar solo la interseccion entre ambas tablas, pero no se porque me saca tantos...


EDIT: me he fijado en PROCESSES que pone en comando SLEEP y en estado CLEANING UP ¿?

[seronellas]

Bueno gente, parece ser que al final di con la solución usando INNER JOIN y modificando los campos por los que filtrar, por si a alguien le sirve de ayuda el código definitivo es:

Código:

SELECT DISTINCT * 
FROM stock
INNER JOIN new_log ON stock.id = new_log.id_stock
WHERE stock.id_sucursal =$sucursal
AND stock.id_sucursal = new_log.id_sucursal_destino
AND new_log.fecha_alta
BETWEEN  '$fecha_ini'
AND  '$fecha_fin'
AND new_log.operacion =  'MOVIMIENTO STOCK'

Muchas gracias a todos los que me han ayudado :)

[pancho2413]

Una cosa para 'seronellas': me encantaría conocer qué resultado te dió EXPLAIN con tu consulta, dado que este comando es muy importante para la optimización de consultas, valga la redundancia.