Tengo una aplicación web en la cual muestro los datos de las facturas de clientes, junto con el nombre de cliente. Desde un principio observe que la consulta un poco mas de lo normal, asi que me puse a investigar.
Propiedades de las tablas:
La tabla clientes tiene los tipicos campos, los que nos interesan son Id y Nombre, y luego el Id es PrimaryKey, NotNull, AutoIncrement, y tiene 732 registros
La otra tabla es regfacturas, en la cual se registran los datos importantes de las facturas, los campos que nos interesan son el id y el idCliente, el id es PrimaryKey, NotNull, AutoIncrement y el idCliente no esta definido como ForeingKey, luego probaremos a definirlo a ver que resultados nos salen. Esta tabla tiene 4950 registros.
Las dos tablas son InnoDB y como collation utf8, la version del servidor es 5.1.58 y voy a efectuar las consultas en la consola de MySQL.
Nota: Para que los resultados sean efectivos es recomendable efectuar un vaciado de la cache de consultas:
flush query cache;
La consulta original es:
Select r.id as id,r.codigo as codigo,r.fecha as fecha,r.importe as importe ,r.obs_alt as obs_alt, c.Nombre as nombre from regfacturas as r join clientes as c on r.id_cliente like c.id order by r.fecha desc
Los resultados de esta consulta fueron: 4945 rows in set (1.71 sec)
Visto estos tiempos, me plantee modificar la consulta y hacerla con subquery
La consulta con subquery es:
Select id, codigo, id_cliente as idCliente, date_format(fecha,'%d-%m-%Y') as fecha, importe, obs_alt as observaciones, (Select Nombre from clientes where id = id_cliente) as cliente FROM regfacturas order by fecha desc
Los resultados de esta consulta fueron: 4950 rows in set (0.16 sec)
Vistos estos resultados uno dice menuda diferencia entre la subquery y el join, entonces para que usar join, a partir de ahora todo a subquerys. Antes de lanzarnos como locos vamos a afinar un poco mas.
Vamos a probar ahora la consulta con join pero sin la ordenacion de fechas a ver si es eso lo que hace que funcione lenta
Select r.id as id,r.codigo as codigo,r.fecha as fecha,r.importe as importe ,r.obs_alt as obs_alt, c.Nombre as nombre from regfacturas as r join clientes as c on r.id_cliente like c.id
Los resultados de esta consulta fueron: 4945 rows in set (1.68 sec)
Parece que sin la ordenacion de fecha ganamos algo de velocidad, aunque el tiempo de respuesta sigue siendo inaceptable
Probemos ahora con la subquery
Select id, codigo, id_cliente as idCliente, date_format(fecha,'%d-%m-%Y') as fecha, importe, obs_alt as observaciones, (Select Nombre from clientes where id = id_cliente) as cliente FROM regfacturas
Los resultados de esta consulta fueron: 4950 rows in set (0.12 sec)
Con estos resultados vemos que la ordenación tanto en una como en otra tabla tiene un coste de entre 0.03 – 0.04 , y que con esta estructura de tabla es muchisimo mas rapido el uso de subquery que de inner joins
Agregando una ForeingKey
Al ir a agregar una ForeingKey al id_cliente me dio el siguiente error
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
Googleando descubri que no se podia agregar por que habia registros en la tabla en la cual queria crear la clave foranea que no estaban en la otra tabla.
Para descubir cuales eran estos registro hize la siguiente consulta:
SELECT COUNT( * ) AS cuenta FROM regfacturas t1 LEFT JOIN clientes t2 ON t1.id_cliente = t2.id WHERE t2.id IS NULL
El resultado de esta consulta nos dio 5, lo que nos quiere indicar que tenemos 5 campos que estan mal
A continuacion haciendo la siguiente consulta
SELECT * FROM regfacturas t1 LEFT JOIN clientes t2 ON t1.id_cliente = t2.id WHERE t2.id IS NULL
Salieron los campos que nos daban problemas, los borramos, y ya se pudo establecer la FK.
Ahora que ya la tenemos definida vamos a repetir las consultas a ver que sale
La consulta con inner Join dio el siguiente resultado: 4945 rows in set (1.80 sec)
Y la consulta con subquery: 4945 rows in set (0.13 sec)
Con estos resultados podemos decir que en determinados casos es mas rapido usar subquery que inner join, y que establecer foreing keys hace que la consulta se ralentize sobre todo en el caso del uso de inner join
rubenlacasa.es 