Cuando diseñamos una tabla en MySQL (y en general en cualquier otro SGBD) solemos prestar mucha más importancia a las claves primarias que a las foráneas. Sin embargo las claves foráneas son también muy importantes principalmente por un doble motivo. Por un lado permiten mantener la integridad de la base de datos ante posibles modificaciones o eliminación de datos. Por otro lado aceleran enormemente determinadas combinaciones (joins) de tablas. No obstante, si estamos utilizando MySQL este segundo tema no depende estrictamente de las claves foráneas sino de la unicidad de las claves. Son temas parecidos pero no iguales, ya que para que una campo pueda ser referenciado por la clave foránea de otra tabla debe cumplir el criterio de unicidad, pero que lo cumpla no obliga a que alguna clave foránea tenga que referenciarlo. De esto es de lo que va a tratar este post.
Como el tema va a ser algo complicado de explicar sin unas tablas de datos a las que sujetarnos, voy a partir de tres tablas con las que desarrollaré toda la explicación. Estas tres tablas son las que paso a explicar a continuación.
Tabla de materiales vendidos.
mysql> DESCRIBE mat_materialsx;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| mat_ped | varchar(6) | NO | PRI | | |
| mat_pos | smallint(5) unsigned | NO | PRI | 0 | |
| mat_typ | varchar(6) | YES | | NULL | |
| mat_fin | varchar(2) | YES | | NULL | |
| mat_qty | double unsigned | YES | | NULL | |
+---------+----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
Es una tabla que recoge todos los materiales que se han vendido en algún momento a lo largo de la trayectoria de una empresa. Los campos mat_ped y mat_pos son la clave primaria e identifican el número de pedido de venta y la posición dentro de dicho pedido. No afectarán en nada a nuestro desarrollo, así que podemos olvidarnos de ellos. En cambio los restantes campos serán importantes: mat_typ indica el tipo de material, mat_fin indica el acabado de ese material y mat_qty indica el número de kilos vendidos en ese pedido y posición.
Quedará más claro viendo algunos datos que puede contener esta tabla:
mysql> SELECT * FROM mat_materialsx LIMIT 5;
+---------+---------+---------+---------+---------+
| mat_ped | mat_pos | mat_typ | mat_fin | mat_qty |
+---------+---------+---------+---------+---------+
| 100023 | 1 | 430 | XP | 800 |
| A09085 | 1 | 430 | BA | 3000 |
| A09086 | 1 | 430 | BA | 7380 |
| A09138 | 1 | 409 | 2B | 538 |
| A09139 | 1 | 409 | 2B | 539 |
+---------+---------+---------+---------+---------+
5 rows in set (0.00 sec)
Lo he limitado a 5 resultados pero en realidad en esta tabla es donde estará el enorme volumen de datos, ya que la tabla entera contendrá…
mysql> SELECT COUNT(*) FROM mat_materialsx;
+----------+
| COUNT(*) |
+----------+
| 177047 |
+----------+
1 row in set (0.06 sec)
El objetivo final que querremos conseguir (para lo cual necesitaremos llevar a cabo una consulta) es una agrupación de esos 177.047 materiales vendidos. Para ello agruparemos tanto los tipos de material como los acabados a través de unas tablas de parametrización en las que le diremos qué tipo de material o qué acabado agrupado corresponden a cada tipo de material o acabado original. Estas dos tablas son las que ahora vemos. Primero la de tipos de material.
Tabla de agregación de tipos de material.
mysql> DESCRIBE typ_alltypesx;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| typ_bas | varchar(6) | NO | PRI | | |
| typ_agg | varchar(6) | NO | | | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
Es una tabla extremadamente simple donde sólo consta el tipo de material básico (el original, el que aparece en los pedidos) y el tipo de material agrupado que le corresponde a cada tipo de material básico.
Algunos datos de los que consta son los siguientes:
mysql> SELECT * FROM typ_alltypesx LIMIT 5;
+---------+---------+
| typ_bas | typ_agg |
+---------+---------+
| 14462 | 524 |
| 202 | 202 |
| 301PK | 524 |
| 304 | 304 |
| 304/L | 304 |
+---------+---------+
5 rows in set (0.00 sec)
Así, los materiales 14462 y 301PK quedarán agrupados como material 524. Los 304 y 304/L quedarán como 304. Y así para un total de unos 70 materiales.
Tabla de agregación de acabados.
mysql> DESCRIBE fin_allfinish;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| fin_bas | varchar(2) | NO | PRI | | |
| fin_agg | varchar(2) | NO | | | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
También es una tabla extremadamente simple donde sólo consta el acabado básico (el original, el que aparece en los pedidos) y el acabado agrupado que le corresponde a cada acabado básico.
Algunos datos de los que consta son los siguientes:
mysql> SELECT * FROM fin_allfinish LIMIT 5;
+---------+---------+
| fin_bas | fin_agg |
+---------+---------+
| 1 | 1 |
| 1D | 1 |
| 1P | K3 |
| 2B | 2B |
| 2C | 2B |
+---------+---------+
5 rows in set (0.00 sec)
Así, los acabados 1 y 1D quedarán agrupados como acabado 1. El 2B y el 2C quedarán agrupados como acabado 2B. Y así para un total de 94 acabados.
Primera consulta de agrupación.
Tenemos pues las tres tablas presentadas. Vamos a llevar a cabo una primera ejecución de la consulta de combinación y agrupación que necesitaremos para obtener el resultado deseado y evaluaremos cuánto tiempo ha tardado MySQL en devolvernos el resultado:
mysql> SELECT typ_agg AS type,
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (0.95 sec)
No está nada mal. En apenas un segundo ha hecho los correspondientes joins y las agregaciones de los 177.047 registros para devolvernos lo que deseábamos obtener. Entonces… ¿si estoy defendiendo la importancia de las claves foráneas y sobretodo de la unicidad, no es un contrasentido haber obtenido unos resultados tan buenos? No, no lo es. Tiene una explicación. Fijémonos que estamos haciendo las combinaciones con los cambos typ_bas y fin_bas que son claves principales en sus respectivas tablas y que por tanto son campos únicos en ellas. Dicho de otro modo, MySQL no necesita estrictamente que existan claves foráneas (en este caso serían mat_typ y mat_fin) referenciando a estas claves primarias para ejecutar la consulta rápidamente. Lo que sí necesita es que exista ese unicidad.
Bien, vamos a complicarlo y se entenderá mejor -espero-. Imaginemos que no estamos trabajando con datos de una sola empresa sino que formamos parte de un pequeño grupo de empresas que comparten base de datos. Así, en las tablas de parametrización de tipos de material y acabados, la clave principal no es únicamente el tipo de material y el acabado, sino que incorporamos también el código de la empresa en cuestión, ya que el material 304/L en una empresa se agrupará como 304 mientras que en otra se agrupará como 524 (es un decir).
De este modo las dos tablas quedarán ahora así.
Tabla de agregación de tipos de material.
mysql> describe typ_alltypesx;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| typ_cpy | varchar(3) | NO | PRI | | |
| typ_bas | varchar(6) | NO | PRI | | |
| typ_agg | varchar(6) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Con unos datos como estos (AMG es el código de una de las empresas, concretamente de la que nos interesará, ya que pondremos absolutamente todos los datos con este código de empresa):
mysql> SELECT * FROM typ_alltypesx LIMIT 5;
+---------+---------+---------+
| typ_cpy | typ_bas | typ_agg |
+---------+---------+---------+
| AMG | 14462 | 524 |
| AMG | 202 | 202 |
| AMG | 301PK | 524 |
| AMG | 304 | 304 |
| AMG | 304/L | 304 |
+---------+---------+---------+
5 rows in set (0.00 sec)
Tabla de agregación de acabados.
mysql> describe fin_allfinish;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| fin_cpy | varchar(3) | NO | PRI | | |
| fin_bas | varchar(2) | NO | PRI | | |
| fin_agg | varchar(2) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Con unos datos como estos:
mysql> SELECT * FROM fin_allfinish LIMIT 5;
+---------+---------+---------+
| fin_cpy | fin_bas | fin_agg |
+---------+---------+---------+
| AMG | 1 | 1 |
| AMG | 1D | 1 |
| AMG | 1P | K3 |
| AMG | 2B | 2B |
| AMG | 2C | 2B |
+---------+---------+---------+
5 rows in set (0.00 sec)
Segunda consulta de agrupación.
Tenemos pues de nuevo las tres tablas presentadas, sólo que ahora las dos últimas han sufrido un pequeño cambio, aunque aparentemente no demasiado importante puesto que sólo hemos añadido un campo adicional, pero no hemos tocado los que ya había y siguen siendo claves primarias los que lo eran anteriormente. Vamos a llevar a cabo la segunda ejecución de la consulta de combinación y agrupación y evaluaremos cuánto tiempo ha tardado esta vez MySQL en devolvernos el resultado:
mysql> SELECT typ_agg AS type,
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (21.02 sec)
Nada menos que 21 segundos. Una barbaridad. De acuerdo que tiene que combinar y agrupar 177.047 registros, pero tampoco son tantos… ¿qué pasará cuando sean millones? El problema que nos ha aparecido es ni más ni menos que la unicidad. Antes los campos typ_bas y fin_bas que se utilizan para los joins eran únicos. Ahora ya no lo son. Vamos a intentar solucionarlo.
Establecimiento de unicidad.
Para ello ejecutaremos las siguientes sentencias:
mysql> ALTER TABLE typ_alltypesx
-> CHANGE typ_bas typ_bas VARCHAR(6) UNIQUE;
Query OK, 70 rows affected (0.03 sec)
Records: 70 Duplicates: 0 Warnings: 0
mysql> DESCRIBE typ_alltypesx;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| typ_cpy | varchar(3) | NO | PRI | | |
| typ_bas | varchar(6) | NO | PRI | | |
| typ_agg | varchar(6) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE fin_allfinish
-> CHANGE fin_bas fin_bas VARCHAR(2) UNIQUE;
Query OK, 94 rows affected (0.03 sec)
Records: 94 Duplicates: 0 Warnings: 0
mysql> DESCRIBE fin_allfinish;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| fin_cpy | varchar(3) | NO | PRI | | |
| fin_bas | varchar(2) | NO | PRI | | |
| fin_agg | varchar(2) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Aparentemente las tablas no han cambiado. Al menos no nos muestra cambios en el DESCRIBE. Sin embargo sí que lo han hecho, ya que le hemos establecido que los campos typ_bas y fin_bas además de formar parte de claves primarias, son en sí mismos únicos. Si quisiéramos que claves foráneas de otras tablas hicieran referencia a estos campos ahora podríamos hacerlo (antes no habríamos podido ya que MySQL nos habría devuelto un error), pero en realidad para evaluar la velocidad de la consulta no nos es estrictamente necesario. Ya he comentado antes que para la velocidad a la hora de combinar y agrupar MySQL requiere unicidad, no necesariamente claves foráneas. Vamos a verlo.
Tercera consulta de agrupación.
Respecto a la ejecución anterior sólo hemos cambiado que ahora las dos tablas de parametrización han visto como sus campos clave utilizados en los joins se han hecho únicos. Veamos cómo va la tercera ejecución de la consulta de combinación y agrupación y evaluaremos cuánto tiempo ha tardado ahora MySQL en devolvernos el resultado:
mysql> SELECT typ_agg AS type,
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (1.43 sec)
Genial. Hemos pasado de 21 segundos a apenas uno y medio. La mejora es espectacular… ¿Qué ocurriría ahora si aprovechamos que esos campos ya son únicos para hacer que también sean el destino de claves foráneas en la tabla principal? Veámoslo.
Establecimiento de claves foráneas.
mysql> ALTER TABLE mat_materialsx
-> ADD FOREIGN KEY(mat_typ) REFERENCES typ_alltypesx(typ_bas);
Query OK, 177047 rows affected (3.71 sec)
Records: 177047 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE mat_materialsx
-> ADD FOREIGN KEY(mat_fin) REFERENCES fin_allfinish(fin_bas);
Query OK, 177047 rows affected (4.10 sec)
Records: 177047 Duplicates: 0 Warnings: 0
Cuarta consulta de agrupación.
¿Habremos mejorado algo al incorporar estas claves foráneas? Veamos, veamos…
mysql> SELECT typ_agg AS type,
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (1.45 sec)
En este caso la respuesta es que no. Nos hemos quedado exactamente igual. No obstante con todo esto no quiero decir que las claves foráneas no sean necesarias, ya que como he dicho al comenzamiento del post, nos sirven para mantener la integridad de la base de datos (¡aspecto fundamental!). Lo único que pretendo con este post es dejar claro que MySQL no las necesita estrictamente para combinar eficientemente distintas tablas, ya que es capaz de hacerlo igual de bien simplemente con que los campos que utilizamos para los joins sean únicos.
Nota importante.
A lo largo de todo el ejemplo es básico ir borrando la caché de consultas de MySQL para no obtener resultados distorsionados. Para ello he utilizado la sentencia:
mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)
De lo contrario nos encontraremos con tiempos de respuesta maravillosos de 0.00 sec… 
Miniapunte que puede parecer absurdo pero que me ha hecho perder un ratito. El caso es que estaba intentando conseguir que un atributo de tipo Integer sin inicializar tuviera un valor nulo, para así poder insertarlo en un registro de una tabla de base de datos como NULL, ya que dada la naturaleza del atributo, un valor 0 (que es como un atributo de tipo Integer queda automáticamente inicializado) y un valor NULL significan cosas muy distintas. Pero resulta que no se puede hacer, ya que Integer no es una clase en VisualBasic.NET sino sólo un tipo de datos.
Como cualquier autónomo, mis necesidades de desplazamiento son constantes y bastante ineludibles, así que cuando hace cosa de un par de meses mi coche dijo de golpe y sopetón hasta aquí hemos llegado, se me planteó un problema urgente que resolver. Me decidí a contratar un vehículo de renting a través de 
Minipost para comentar que ayer recibí mi primer pedido en 
Vaya por delante que en general me produce más simpatía como empresa Google que Microsoft. También es cierto que creo que en algunos puntos no andan tan lejos la una de la otra, y que si Microsoft hubiera hecho algunas de las cosas que Google ha llevado a cabo nos habríamos lanzado a su yugular, mientras que con Google somos mucho más permisivos y tolerantes. Pero bueno, se habrán ganado ese margen de crédito, no me parece mal.
Hoy algo de teoría. Como sabemos, el lenguaje estándar para realizar consultas en bases de datos es el SQL (Structured Query Language). Este lenguaje permite definir y manipular bases de datos relacionales, que son las bases de datos con las que solemos trabajar (como 
Comentaba 
Como vengo haciendo siempre, paso a publicar las estadísticas de visitas a este blog del mes recién cerrado. Si alguien está interesado en realizar el seguimiento, puede hacerlo a través del 
Hace unas semanas publiqué el post 
