Left join muy lento

[juansoller75]

Buenas compañeros,

Tengo un problema con una consulta que se me relentiza muchisimo. Probando con poner y quitar el LEFT JOIN he visto que este es el motivo de la extrema lentitud. Actualmente las dos tablas que comparten esta consulta tienen 62.000 filas una y 54.000 filas la otra.

He estado investigando y una posible solución es la utilización de indices, creeis que esta es la mejor opción o sería mejor plantear la consulta de otra manera.

Dejo el código aquí y mil gracias

Código MySQL:

SELECT 
can.cand_intnacionalidad Nacionalidad
,DATE_FORMAT(can.cand_datenacimiento,'%d/%m/%Y') as fechaNacimiento
,can.cand_intprovincia as idProvincia
,can.cand_strpoblacion as Poblacion
,can.cand_strfoto as urlFotografia
,can.cand_intautonomo as boolAutonomo
,can.cand_strfoto as urlFotografia 
,cuen.nombre
,cuen.apellido
FROM tbcandidatos can
LEFT JOIN catcuentas cuen on can.ccta_stremail=cuen.ccta_stremail
 

[gnzsoloyo]

Si los campos tbcandidatos.ccta_stremail y catcuentas.ccta_stremail no representan una relación PK<--FK, entonces te conviene indexarlos en ambas tablas.

[juansoller75]

Hola gnzsoloyo,

Muchas gracias por la rapidez.

Cuando te refieres a que no representan una relacion PK<-- FK te refieres a las claves foraneas no? Sería eso una solución distina a los indices? Puedo generar esta relación entre las tablas ?

Gracias y disculpa la ignorancia ya que este tema lo tengo muy verde

[gnzsoloyo]

Las claves foráneas no son estrictamente una solución "distinta". El tema pasa por el lado de que cuando existe una relacion de PK/FK MySQL crea un índice que administra esa relación, por lo que indexar esos campos es redundante. No generará diferencias de comportamiento, y como mucho bajará la performance de los INSERT a las dos tablas.
Ahora bien, si las tablas no son InnoDB, sino MyISAM, entonces no existirán los indices de FOREIGN KEY y te convendrá crear un indice para ese campo en cada tabla.
De todos modos, recuerda que un LEFT JOIN siempre realiza un FULL TABLE SCAN sobre la primera tabla, por lo que si la primera tiene demasiados registros es mejor que se encuentre optimizada.

[juansoller75]

Muchas gracias gnzsoloyo,

Las dos tablas son InnoDB, por lo que he intentado crear una tabla foranea. El problema que me encuentro es que el sistema no me deja por un problema de integridad referencial. Supongo que alguna fila de la clave foranea no existe en la clave principal.

Por este motivo he intentado detectar estas filas para eliminarlas (Leiendo una propuesta tuya que le hacías a otro compañero: http://www.forosdelweb.com/f86/como-comprobar-que-campo-tabla-exista-otra-tabla-785427/) pero el sistema se me queda colgado entiendo que por ser una consulta muy muy lenta. Existe otra manera de comprobar las filas que no coinciden?

Mil gracias

[gnzsoloyo]

Dos formas básicas:

Código MySQL:

SELECT can.ccta_stremail
FROM tbcandidatos can
LEFT JOIN catcuentas cuen on can.ccta_stremail=cuen.ccta_stremail
WHERE cuen.ccta_stremail IS NULL
 

eso te devolverá el campo ccta_stremail de aquellos registros de la primera que no tengan su correspondiente en la segunda.
o bien

Código MySQL:

SELECT can.ccta_stremail
FROM tbcandidatos can
WHERE cuen.ccta_stremail NOT IN (SELECT cuen.ccta_stremail FROM catcuentas cuen )
 

Respecto a las FK recuerda que para crear una:
1) El campo de la taba referida debe ser PRIMARY KEY o al menos UNIQUE en su tabla.
2) Ambos campos deben ser del mismo tipo, rango, collation y charset, si son de caracteres, o tipo y rango, si son numéricos, fecha, hora o datetime.
3) Si tienen datos, deben ser consistentes con las restricciones de FK (no debe haber valores en la segunda que no existan en la PK de la primera).

[juansoller75]

Hola gnzsoloyo,

El primer código ya lo había intentado y en el segundo me pasa exactamente igual. En phpmyadmin me aparece después de un tiempo el error #1317 - Query execution was interrupted .

Entiendo que la busqueda es muy pesadada y por eso. Existe alguna otra manera menos "pesada" o la opción de hacerlo en tramos para que me lo permita realizar?

Muchas gracias por tu ayuda

[gnzsoloyo]

Bueno, vamoa a analizar el problema mas en detalle. VEamos la composicion de las tablas. Postea el resultado de estas dos queries (son para ver la estructura real de las tablas):

Código MySQL:

SHOW CREATE TABLE tbcandidatos;
SHOW CREATE TABLE catcuentas;
 

Luego esto:

Código MySQL:

SELECT COUNT(ccta_stremail) TotalNoNulos, SUM(IF(ccta_stremail IS NULL OR TRIM(ccta_stremail ) = '', 1, 0)) TotalNulos
FROM tbcandidatos
 

Código MySQL:

SELECT COUNT(ccta_stremail) TotalNoNulos, SUM(IF(ccta_stremail IS NULL OR TRIM(ccta_stremail ) = '', 1, 0)) TotalNulos
FROM catcuentas;
 

[juansoller75]

SHOW CREATE TABLE tbcandidatos

Código MySQL:

CREATE TABLE `tbcandidatos` (
 `ccta_stremail` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
 `sin_estudios` int(1) NOT NULL,
 `sin_experiencia` int(1) NOT NULL,
 `cand_datenacimiento` date NOT NULL,
 `cand_intgenero` tinyint(4) NOT NULL,
 `cand_strtelefono` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
 `cand_intpais` tinyint(4) NOT NULL,
 `cand_intprovincia` tinyint(4) NOT NULL,
 `cand_strpoblacion` varchar(150) COLLATE latin1_spanish_ci NOT NULL,
 `cand_intnacionalidad` tinyint(4) NOT NULL,
 `cand_intautonomo` tinyint(4) NOT NULL,
 `cand_intconductor` varchar(200) COLLATE latin1_spanish_ci NOT NULL,
 `cand_strfoto` varchar(150) COLLATE latin1_spanish_ci NOT NULL,
 `cand_inactivo` tinyint(4) NOT NULL,
 `cand_strpuestoideal` varchar(200) COLLATE latin1_spanish_ci NOT NULL,
 `cand_intcategoria` tinyint(4) NOT NULL,
 `cand_strsubcategorias` varchar(300) COLLATE latin1_spanish_ci NOT NULL,
 `cand_strprovincias` varchar(300) COLLATE latin1_spanish_ci NOT NULL,
 `cand_intviajar` tinyint(4) NOT NULL,
 `cand_intmudanza` tinyint(4) NOT NULL,
 `cand_intofertas` tinyint(4) NOT NULL,
 `cand_intperfil` tinyint(4) NOT NULL,
 `fech_last_login` date NOT NULL,
 `fech_last_postu` date NOT NULL,
 `fecha_last_pdf` date NOT NULL,
 `fecha_las_linkedin` date NOT NULL,
 `almacen` int(1) NOT NULL,
 `integridadRelacional` int(1) NOT NULL,
 PRIMARY KEY (`ccta_stremail`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
 

SHOW CREATE TABLE catcuentas

Código MySQL:

CREATE TABLE `catcuentas` (
 `id_cuenta` int(255) NOT NULL AUTO_INCREMENT,
 `ccta_stremail` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
 `ccta_strnombre` varchar(200) COLLATE latin1_spanish_ci NOT NULL,
 `ccta_strapellidos` varchar(250) COLLATE latin1_spanish_ci NOT NULL,
 `ccta_strtelefono` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
 `ccta_strpassword` varchar(20) COLLATE latin1_spanish_ci NOT NULL,
 `ccta_md5strpassword` varchar(555) COLLATE latin1_spanish_ci NOT NULL,
 `ccta_inttipo` tinyint(4) NOT NULL COMMENT '1. Empresa - 2.Candidato',
 `ccta_intactivo` tinyint(4) NOT NULL,
 `codigo` varchar(20) COLLATE latin1_spanish_ci NOT NULL,
 `fecha_alta` date NOT NULL,
 `cookie` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
 PRIMARY KEY (`id_cuenta`,`ccta_stremail`)
) ENGINE=InnoDB AUTO_INCREMENT=68664 DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
 

SELECT COUNT tbcandidatos

TotalNoNulos TotalNulos
54911 1

SELECT COUNT catcuentas

TotalNoNulos TotalNulos
67772 1


Un saludo,