Query con LEFT JOIN tarda mucho en MySQL

[Antonio Cardinaux]

Quiero hacer un SELECT con un JOIN de 2 tablas, la primer tabla tiene varios campos, pero los que aparecen en el SELECT son:

Tabla1

codigo  Varchar(8)
cliente Varchar(6)
fecha   Date
importe Decimal(6,2)

y en Tabla2

cliente Varchar(6)
nombre  Varchar(30)

La tablas tienen ambas un indice por el campo "cliente", entre otros indices. Tabla1 tiene 550000 registros y Tabla2 7000.
El select es este:

select codigo,tabla1.cliente,tabla2.nombre,sum(tabla1.importe) as importe from tabla1 LEFT JOIN tabla2 ON tabla1.cliente=tabla2.cliente where fecha between '2020-11-01' and '2020-11-30' and codigo <> '' group by codigo

Si uso INNER JOIN el query tarda 0,56 seg., pero como el campo cliente de la tabla1, puede tener o no un código de cliente, tengo que usar LEFT JOIN, y ahi ya de 0,56 seg. pasa a 1 min. 15 seg. Bueno no saco el porque de tanta diferencia de tiempo.
Como dato adicional puedo decir que ambas tablas son MYISAM, si es que eso sirve para este caso, y que el entorno en donde estoy usando MySQL es Windows 7 como cliente y Windows 2008 Server como servidor, la versión de MySQL es la 5.6.

Antonio
Sistemas
Resipol

[Nippur Lagash]

Hola, usa explain en el select así ves dónde tarda el left join.

Si bien las tablas myisam son más rápidas (menos inteligentes) no soportan transacciones.

Innodb es más aconsejable para manejar muchos datos.

Saludos,

Fer.MDQ

--
Has recibido este mensaje porque estás suscrito al grupo "[oohg]" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a oohg+uns...@googlegroups.com.
Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/789129b4-b755-4841-acb5-466080effad3n%40googlegroups.com.

[Nippur Lagash]

Por otro lado no es aconsejable tener id de cliente como varchar (se demora más al comparar cadenas que números), deberías tener un id numérico para relacionar.

Agregaría un campo cliente_id int(10) para hacer la relación y dejar el otro por si lo debes conservar por otros motivos.

Saludos,

Fer.MDQ

--

Nippur
Mar del Plata
Buenos Aires
Argentina

https://mozit.com.ar

[Sergio Castellari [Gmail]]

Hola,

 

Iba a contestar pero veo que las contestaciones de Nippur son las que pensaba ¡!

 

Abrazos Fernando!!!

 

Saludos,

Sergio

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CA%2BYc3wvZW-Fbu8n4cbg_%2BUjETaFJ8XGDA88VS3RoXRTUOVBJQA%40mail.gmail.com.

[Antonio Cardinaux]

Gracias Nippur. Ahi corrí el EXPLAIN y me dió esto:

| id | select_type | table    | type  | possible_keys      | key      | key_len | ref  | rows | Extra                                        |
+----+-------------+----------+-------+--------------------+----------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | cajamov  | range | ix_fecha,ix_codigo | ix_fecha | 4       | NULL | 1857 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | clientes | ALL   | NULL               | NULL     | NULL    | NULL | 6939 |                                              |

A decir verdad nunca entendí bien como "trasladar" lo que aparece en el EXPLAIN a algo práctico..., no se si se entiende lo que quiero decir, en otras palabras, no entiendo bien lo que me dice.

El tema con el campo cliente, es que es un campo varchar porque lo que estoy haciendo es pasar tablas DBF con campos similares, o sea en la tabla DBF clientes el campo es Character(6). Tendría que agregar un campo ID_CLIENTE en la tabla CAJAMOV, que debería relacionar con el ID_CLIENTE de la tabla CLIENTES, esto ya estoy hablando de la tabla en MySQL, es así lo que me querés decir no, porque ahi ya tendría que migrar la tabla DBF de clientes y después hacer otro proceso para pasar el ID de la tabla clientes a la tabla CAJAMOV (según el ejemplo). Bueno igual Gracias.

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CA%2BYc3wvZW-Fbu8n4cbg_%2BUjETaFJ8XGDA88VS3RoXRTUOVBJQA%40mail.gmail.com.

--

Antonio Cardinaux - Lanús - Pcia. de  Buenos Aires - Argentina

[Nippur Lagash]

Fijate si de acá podes sacar alguna conclusión:

https://es.stackoverflow.com/questions/53779/mysql-para-qu%C3%A9-sirve-explain-en-una-consulta-y-c%C3%B3mo-interpretar-los-resultados

Tu consulta, le agregaría un order

select 

   codigo,

   tabla1.cliente,

   tabla2.nombre,

   sum(tabla1.importe) as importe 

from tabla1 

LEFT JOIN tabla2 ON tabla1.cliente=tabla2.cliente 

where fecha between '2020-11-01' and '2020-11-30' and codigo <> '' 

group by codigo  

le agregaria

order by tabla1,.codigo,tabla1.cliente,tabla1.fecha  (no se si así se llaman los campos)

chequeá si con eso mejora algo

Saludos,

Fer.MDQ

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CAHAYtudG%3Dh_84y%3DFQjo1diHksfEOJXkiUfTnseK0GEa-6ayOsg%40mail.gmail.com.

[Antonio Cardinaux]

Bueno, me va a llevar un buen rato ver toda la información y realizar pruebas, en cuanto tenga algo te comento. Gracias Nippur.

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CA%2BYc3wtQCzkugVnhHdQaRTt8NhrHRrhNoTBmV0SBZoYvvVUxCA%40mail.gmail.com.

[Nippur Lagash]

Según tu explain, sobre caja_mov usa índices, pero no sobre customers.

Probá también si con el ORDER BY <campos> te agiliza la query.

Saludos,

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CAHAYtueAAS0C-EqJNeo4DjYvCWbdEpwHy3nwkcMnyiosbyfRWg%40mail.gmail.com.

[Winston Garcia]

Hola,

Valida tambien que los campos que filtras en el where esten en elmindice.

Saludos

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CAHAYtueAAS0C-EqJNeo4DjYvCWbdEpwHy3nwkcMnyiosbyfRWg%40mail.gmail.com.

[David Field]

Hola Antonio,

Basado en tu información, toma en cuenta que el SELECT que estás usando realiza 550,000 búsquedas, aparte de eso, me parece raro que MySQL no te tire error por solo estar agrupando por el codigo.

Podemos reducir esas búsquedas a un máximo de 7000, que es el número de registros que tienes en tabla2 si cambiamos la instrucción SQL

SELECT a.codigo, a.cliente, b.nombre, a.importe FROM

(SELECT codigo, cliente, sum(importe) as importe 

 FROM tabla1

 WHERE fecha BETWEEN '2020-11-01' AND '2020-11-30' AND codigo <> '' 

 GROUP BY codigo, cliente ) a

LEFT JOIN tabla2 b ON a.cliente = b.cliente

De esta forma, realizas primero tu sumatoria por codigo y cliente y SOLO del resultado hacemos la búsqueda para el nombre del cliente.

Y como no sabemos cuando SQL va a usar índices, sería interesante ver si cambiando a GROUP BY cliente, codigo te funciona mejor.

Saludos,

David Field

[Antonio Cardinaux]

Este ejemplo que pusiste redujo el tiempo a 16 seg y algo, bajó muchísimo del minuto y tantos que tardaba el original. Es muy bueno eso David. Gracias.

Lo que si habia leido hace un tiempo, no se cuanto de esto, es que si un Query tardaba más de 5 segundos en procesar, es porque algo había que corregir, quizás haya sido exagerado lo que había leído, pero por ahi falten hacer otras pruebas. Gracias de nuevo.

--
Has recibido este mensaje porque estás suscrito a un tema del grupo "[oohg]" de Grupos de Google.
Para cancelar la suscripción a este tema, visita https://groups.google.com/d/topic/oohg/KW1mtdtynj0/unsubscribe.
Para cancelar la suscripción a este grupo y a todos sus temas, envía un correo electrónico a oohg+uns...@googlegroups.com.
Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/c166591d-9089-4419-bde8-9a878c042f01n%40googlegroups.com.

[Antonio Cardinaux]

Hola Winston. Ya tengo indices creados por los campos del where, son índices individuales, o sea hay un índice por código, otro por cliente y otro por fecha, pero estan.

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CAA7ca-oF%2BxgQ0CFd7T0NBTu0pR2sz_HscNkC3rKvxtErG6qSZQ%40mail.gmail.com.

[Antonio Cardinaux]

Ahi también hice la prueba del order by, ya sobre el ejemplo que me propuso David, y no cambió el tiempo de proceso.

Con respecto a lo que decis de que customers no usa indices, es a eso a lo que iba cuando decia que no entendia bien el explain, porque indice tiene esa tabla, el del campo cliente (el de varchar), es uno solo pero tiene. Bueno igual voy a leer lo que me mandaste de EXPLAIN. Gracias.

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CA%2BYc3wskVuzevUs2pCXuP5mKfyYJUYyLMaPdPM-mx2Amjt1_tw%40mail.gmail.com.

[David Field]

Antonio,

En SQL no he tenido 'casi' necesidad de implementar indices.

Los pocos que tengo son para las claves de clientes, proveedores, productos y todos ellos en las tablas donde se definen (clientes, proveedores, invent)

Acabo de realizar una prueba similar a la tuya con una tabla que contiene 390,000 registros y me tarda 0.6 segundos.

Esta tabla contiene un registro por movimiento de inventario realizado, esto incluye ajustes, ventas, compras, devoluciones, tomas de inventario, etc.

SELECT claveinv, sum(cant) as cantidad, b.descrip FROM movinv a

LEFT JOIN invent b ON b.clave = a.claveinv

WHERE fecha BETWEEN '2019-01-01' AND '2019-06-01'

GROUP BY  claveinv

No tengo ningún indice para movinv y en invent tengo indice por clave

Haz realizado prueba de tu query borrando los indices a tabla1?

Saludos,

David Field

[José M. C. Quintas]

Considere normal como sendo 3 segundos.

Procure usar chaves numéricas que fica mais rápido.

Antes eu convertia pra DBF, e demorava mais pra converter do que pra retornar consulta.

Hoje uso apenas ADO + MySQL

Aqui um browse com cerca de 30.000 registros, trás tudo de uma vez, e filtro enquanto digita.

é GTWVG + ADO + MySQL

https://www.youtube.com/watch?v=-yl_RxQgaqM&list=PLDVZ9887sLmv_BlaJqBHmRMmSJXQzF7cw&index=1

José M. C. Quintas

Has recibido este mensaje porque estás suscrito al grupo "[oohg]" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a oohg+uns...@googlegroups.com.
Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/CAHAYtufObdxGZcYOuNzxw0w4zBUbjmKzhr4aGsptKyawYtb7UA%40mail.gmail.com.

[Antonio Cardinaux]

Con el arreglo que me propuso David, anduvo todo bien. El tema fue que tenia que agregar una tabla de conceptos de tesorería, y ahí volvió a ralentizarse el proceso. Después de leer el apunte que me pasó Nippur, y haciendo algunas pruebas, al final agregué un campo nuevo con el ID de la tabla de conceptos de tesorería, en la tabla con más movimientos, y luego agregué un proceso para actualizar ese campo nuevo con el ID del código que aparecía en la tabla de conceptos de tesorería, algo como:

UPDATE cajamov INNER JOIN motivos ON cajamov.codigo=motivos.codigo SET cajamov.motivos_id=motivos.motivos_id WHERE cajamov.codigo <> ''

Bueno con eso se redujo el tiempo de proceso a 2 segundos y monedas, y listo.
Supongo que el problema estaba en que el código (NO el ID), de la tabla de conceptos de tesorería es un VARCHAR(8), que era algo que me decía Nippur en una de las respuestas.
Bueno tema solucionado. Gracias por los aportes.

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/oohg/27b38fd3-4d30-6b44-01bd-9ae722f3c13d%40gmail.com.