Consultando la BD: SELECT

En este apartado voy a explicar las consultas simples en SQL. La forma básica de un SELECT es:

SELECT [opciones] valores 
FROM tablas 
WHERE condiciones 
[ORDER BY  campos]

Analizando la sintáxis tenemos

• opciones: según el SGBD que usemos tendremos opciones para establecer la prioridad de la consulta, obtener resultados no duplicados, y opciones útiles como el SQL_CALC_FOUND_ROWS de MySQL, que calcula cúantos registros devuelve la consulta sin aplicarle un LIMIT (así podemos obtener por ejemplo 20 registros, ideal para una paginación, y saber cuántos registros devolvería sin el LIMIT lanzando después un SELECT FOUND_ROWS()).
• valores: son las columnas con valores que devolverá la consulta. Aquí podemos indicar de todo: nombres de campos, operaciones aritméticas, valores constantes, funciones, subconsultas... y también así, podemos asignarles un alias para que se devuelvan con ese nombre de columna ("SELECT campo1 c1 FROM tabla" nos devolvería el campo1 con el nombre de columna c1). En los alias, dependiendo del SGBD, se puede usar el keyword opcional AS, que es algo más claro a la vista: SELECT campo1 AS c1 FROM tabla
• tablas: aquí van todas las tablas que participarán en la consulta. Pueden ir tanto tablas (y vistas), como subconsultas, y se pueden usar aliastambién (el keyword AS es menos soportado aquí, ya que por ejemplo Oracle no lo admite). Una subconsulta significa poner una consulta entre paréntesis y usar su resultado como una tabla normal:

  SELECT campo1, sc1, sc2 
  
  FROM tabla, (SELECT campo2 AS sc1, campo2  AS sc2 FROM tabla2) 
  
  WHERE campo1 = sc1

  Algunos SGBD como MySQL permiten varias opciones en este parte, como indicar qué índice queremos que se use para recorrer la tabla. Esto último es muy raramente útil pues el optimizador de consultas no suele errar en el índice a usar; es más común que se equivoque en el orden en el que une las tablas, pero para eso hay opciones como STRAIGHT_JOIN en el SELECT que obligan al optimizador a unir las tablas en el orden en el que las hemos puesto nosotros. Las JOIN van en este lugar también pero más adelante las veremos.
• condiciones: serie de condiciones que en conjunto tienen que evaluar a TRUE para que el registro sea devuelto. Debido al orden en que se procesan las consultas, aquí no se pueden usar los alias de los campos que hayamos indicado en la parte de "valores" (pero sí los alias de tablas), así que tenemos que usar el nombre del campo o poner de nuevo la operación entera si se trata de una operación. Son estándares expresiones como IS NULL/IS NOT NULL (devuelve TRUE si lo evaluado es/no es nulo), NOT (niega la expresión, de modo que si evalúa a TRUE no será devuelto el registro), BETWEEN valor1 AND valor2 (el operando precedente tiene que estar entre los valores indicados, inclusive), LIKE 'patrón' (el operando, que debe ser una cadena, debe coincidir con el patrón, que admite, comodines como el '%' para cualquier carácter de 0 a N veces y el '_' para cualquier carácter una sola vez)... y como no, también podemos usar funciones del SGBD y operaciones de cualquier tipo, además de los operadores >, >=, <>, <, <=.Como operadores lógicos tenemos el NOT, AND y OR (con ese orden de preferencia), y para no marearnos en las expresiones complicadas podemos meter entre paréntesis las expresiones:

  ... WHERE (condicion1 OR condicion2) AND condicion3 AND condicion4

  Aquí es donde filtraremos los resultados para obtener lo que nos interese, como los usuarios que tienen el nombre Miguel, los trabajadores que tienen más de 30 años, las líneas de pedido de un pedido concreto...
• ORDER BY: aquí especificamos el orden de los registros segun el campo que queramos. Aquí sí se puede usar alias de campos, o el número de posición que ocupa el valor dentro de la fila:

  SELECT campo1 AS c1, campo2 FROM tabla ORDER BY 2

  En el ejemplo anterior se ordenarían los registros por campo2, que es la columna número 2 del resultado También se puede ordenar por varios campos, y en ascendente o descendente:

  ... ORDER BY campo1 DESC, campo2 ASC

  El ORDER BY en ocasiones puede hacer que el resultado tarde más en ser devuelto pues el SGBD puede necesitar realizar otra pasada para ordenarlo.

Limitando el número de registros devueltos

Es frecuente que al consultar queramos obtener un número máximo de registros, por ejemplo para realizar una paginación o simplemente porque queremos obtener el último registro insertado y no queremos andar con consultas complejas para sacarlo. Todos los SGBD tienen su método para realizar esto: MySQL tiene el LIMIT (haciendo SELECT * FROM tabla WHERE ... ORDER BY ... LIMIT [10 | 5, 10] obtendríamos los 10 primeros registros o los 10 registros a partir del número 5 con la segunda sintaxis), MSSQL Server tiene el TOP (con SELECT TOP 10 ... FROM tabla sacaríamos los 10 primeros registros), Oracle usa el ROW_NUMBER (mediante subconsultas; es un sistema bastante más incómodo que los demás, pero es más estándar), PostgreSQL usa también LIMIT (LIMIT 10 OFFSET 5)... El método más estándar es mediante cursores pero no todos los SGBD siguen las directrices del ISO SQL.

Consultando la BD: INNER JOIN

SELECT ... FROM tabla1 INNER JOIN tabla2 ON tabla1.campo = tabla2.campo WHERE ...

Cuando realizamos una INNER JOIN lo que hacemos es unir dos tablas mediante una o varias condiciones, habitualmente porque están relacionadas por un campo clave. Se pueden concatenar tantas JOIN como queramos; de hecho es la manera habitual de seguir las relaciones de las tablas al realizar consultas (una tabla se relaciona con otra, que a su vez se relaciona con otra...). INNER JOIN es exclusivo, de modo que la relación debe cumplirse siempreo sino no se devolverá el registro. Así, si consultamos todos los clientes y unimos los pedidos mediante el ID de cliente obtendremos sólo los que tengan pedidos. Si hacemos:

SELECT campo1 FROM tabla t INNER JOIN tabla2 t2 ON t.id = t2.id_t

sería lo mismo que hacer:

SELECT campo1 FROM tabla t, tabla2 t2 WHERE t.id = t2.id_t

De hecho, los optimizadores de consultas (query optimizer) suelen cambiar las JOINS por consultas así. No es que sea más óptimo realizarlo así, pero para operar sobre las tablas para optimizar la consulta se manejarán mejor así. Pero del lado del programador de BD, es más fácil de ver las uniones mediante JOINsque mirando las tablas del FROM y luego las condiciones del WHERE (al menos para mí).

Las condiciones del JOIN (el ON ...)

Cabe destacar que en la condición del JOIN se puede escribir cualquier tipo de expresión que evalúe a TRUE(o a FALSE, aunque en ese caso no se devolverá el registro): podemos usar LIKE, operaciones aritméticas, operadores >, =... pero no es recomendable pues por razones que el optimizador sabrá suele ser más lento que si las hubiéramos puesto en el WHERE. Por lo tanto:

SELECT campo1 FROM tabla t INNER JOIN tabla2 t2 ON t.id = t2.id_t AND t.campo2 > t2.campo2

es mejor traducirlo a:

SELECT campo1 FROM tabla t INNER JOIN tabla2 t2 ON t.id = t2.id_t WHERE t.campo2 > t2.campo2

Por lo demás, cualquier condición que no sea ligar las tablas por ID es mejor ponerla en el WHERE. También se puede omitir el ON con la condición, pero en ese caso el resultado será en la mayoría de los casos catastrófico, pues si usamos 2 tablas sin relacionarlas se realizará el producto cartesiano entre ellas, o sea que por cada registro de la tabla 1 se devolverán todos los registros de la tabla 2. Si son tablas de 10 y 5 registros no es mucho problema, se devolverían 50 registros, pero si son de 10000 y 50000..., además no sólo será un resultado grande, también muy lento y pesado en recursos para el servidor de BD.

Rendimiento de las JOIN: no olvidarse de las claves

Y por último, el rendimiento de las JOIN. Es conveniente realizar la JOIN mediante campos que sean claves (primarias, candidatas, externas...) pues el SGBD recorrerá las tablas para satisfacer las condiciones de la JOIN, y si se usan índices el recorrido es infinítamente más rápido. Así que es importante que todo campo referencial, que son los que se usan para unir las tablas en las consultas, sean claves.

Ejemplo

Vamos a suponer una BD con clientes, pedidos y productos (1 cliente tiene N pedidos, 1 pedido es de un único cliente, y 1 pedido tiene N productos que a su vez pueden estar en M pedidos). De la relación pedidos - productos nos sale la entidad lineas_pedidos.

clientes -> pedidos -> lineas_pedido <- productos

Tabla clientes:

id_cliente	nombre
2	John
10	Edward
13	Rose

Tabla pedidos:

id_pedido	id_cliente	fecha
1	2	2009-01-24 08:00:15
2	2	2009-01-24 09:22:08
3	10	2009-01-27 13:10:22
4	2	2009-01-27 17:54:15
5	10	2009-01-29 08:13:01
6	10	2009-01-29 10:28:57
7	10	2009-01-29 18:26:59
8	2	2009-01-30 12:30:47
9	10	2009-01-30 12:31:18

Tabla productos:

id_producto	nombre	precio_base	stock
1	frigorífico ACME	749.00	25
2	televisor plasma	799.00	87
3	minicadena HI-FI	129.00	19
4	aspirador ACME	89.00	28
5	climatizador ACME	1499.00	32

Tabla lineas_pedido:

id_pedido	id_producto	precio_unidad	cantidad
1	1	709.00	1
1	4	89.00	2
2	2	729.00	1
3	2	699.00	1
4	2	729.00	1
4	3	129.00	1
4	4	89.00	2
5	2	699.00	7
6	5	1399.00	9
7	4	89.00	5
8	2	729.00	1
9	5	1399.00	8

SELECT * FROM clientes c INNER JOIN pedidos p ON c.id_cliente = p.id_cliente

id_cliente	nombre	id_pedido	fecha
2	John	1	2009-01-24 08:00:15
2	John	2	2009-01-24 09:22:08
10	Edward	3	2009-01-27 13:10:22
2	John	4	2009-01-27 17:54:15
10	Edward	5	2009-01-29 08:13:01
10	Edward	6	2009-01-29 10:28:57
10	Edward	7	2009-01-29 18:26:59
2	John	8	2009-01-30 12:30:47
10	Edward	9	2009-01-30 12:31:18

Como se puede observar no se han devuelto registros del cliente 13 porque no tiene pedidos. Y también se ve que por cada cliente se han devuelto tantos registros como coincidencias tiene en la tabla de pedidos. Si quiseramos obtener sólo un registro por cliente usaríamos GROUP BY para agrupar los registros, pero eso lo veremos más adelante.

SELECT pr.nombre, l.precio_unidad, l.cantidad, (l.precio_unidad * l.cantidad) AS total_linea,

ROUND(100 - ((l.precio_unidad * 100) / pr.precio_base), 2) AS descuento

FROM pedidos p INNER JOIN lineas_pedido l ON p.id_pedido = l.id_pedido

INNER JOIN productos pr ON l.id_producto = pr.id_producto

WHERE p.id_cliente = 2 AND p.id_pedido = 4

nombre	precio_unidad	cantidad	total_linea	descuento
televisor plasma	729.00	1	729.00	8.76
minicadena HI-FI	129.00	1	129.00	0.00
aspirador ACME	89.00	2	178.00	0.00

Lo que hemos hecho ahora es, dado un cliente y un pedido (para asegurarnos de que el pedido es de un cliente en concreto), sacar el detalle de los productos solicitados en dicho pedido. Así podríamos preparar el detalle de la facturacon el nombre del producto, su precio, su cantidad, su precio total y el % de descuento que ha tenido sobre el precio base.

Consultando la BD: LEFT JOIN

SELECT ... FROM tabla1 LEFT JOIN tabla2 ON tabla1.campo = tabla2.campo WHERE ...

La sintaxis de LEFT JOIN es idéntica a INNER JOIN, pero su comportamiento es muy distinto. Con LEFT JOIN obtenemos todos los registros de la izquierda de la expresión ("tabla1" en el ejemplo de arriba) y luego se unirían con la parte derecha, si hubiera coincidencias. Por ejemplo, si unimos clientes con pedidos y un cliente no tiene pedidos, se devolvería el cliente en el resultado pero con todos los campos de la tabla pedidos a NULL. Así es muy fácil obtener todos los clientes que no tienen pedidos:

SELECT ... FROM clientes c LEFT JOIN pedidos p ON c.id_cliente = p.id_cliente WHERE p.campo IS NULL

Por lo demás la LEFT JOIN tiene las mismas características que una INNER JOIN en cuanto a las condiciones que se pueden poner en el ON, y opciones que el SGBD permita.

La variante RIGHT JOIN

Hay una variante no estándar de LEFT JOIN, la RIGHT JOIN, que hace exactamente lo mismo pero devolviendo todos los registros de la derecha de la expresión. Al no ser estándar y ser perfectamentesustituible por la LEFT JOIN (lo único que cambia es el orden en el que pongamos las tablas) no se recomienda usarla.

Ejemplo

Tabla clientes:

id_cliente	nombre
2	John
10	Edward
13	Rose

Tabla pedidos:

id_pedido	id_cliente	fecha
1	2	2009-01-24 08:00:15
2	2	2009-01-24 09:22:08
3	10	2009-01-27 13:10:22
4	2	2009-01-27 17:54:15
5	10	2009-01-29 08:13:01
6	10	2009-01-29 10:28:57
7	10	2009-01-29 18:26:59
8	2	2009-01-30 12:30:47
9	10	2009-01-30 12:31:18

SELECT * FROM clientes c LEFT JOIN pedidos p ON c.id_cliente = p.id_cliente

id_cliente	nombre	id_pedido	fecha
2	John	1	2009-01-24 08:00:15
2	John	2	2009-01-24 09:22:08
10	Edward	3	2009-01-27 13:10:22
2	John	4	2009-01-27 17:54:15
10	Edward	5	2009-01-29 08:13:01
10	Edward	6	2009-01-29 10:28:57
10	Edward	7	2009-01-29 18:26:59
2	John	8	2009-01-30 12:30:47
10	Edward	9	2009-01-30 12:31:18
13	Rose	NULL	NULL

A diferencia del INNER JOIN, hemos obtenido también un registro del cliente 13 aunque no tiene coincidencias en la tabla de pedidos. Ahora, para obtener los clientes que no tienen pedidos simplemente hacemos:

SELECT * FROM clientes c LEFT JOIN pedidos p ON c.id_cliente = p.id_cliente WHERE p.id_pedido IS NULL

id_cliente	nombre	id_pedido	fecha
13	Rose	NULL	NULL

Consultando la BD: GROUP BY

SELECT ... FROM tabla1 INNER JOIN tabla2 ON tabla1.campo = tabla2.campo WHERE ... GROUP BY campo1 [HAVING condiciones] Con GROUP BY podemos agrupar, en torno a uno o varios campos(algunos SGBD admiten alias en vez de campos), los registros devueltos. Opcionalmente podemos usar condiciones del agrupamiento en el HAVING, algo típicamente usado con las funciones de agregado.

Funciones de agregado (COUNT, SUM, MAX, AVG...)

Cuando agrupamos los registros (no hay por qué usar JOINs, se puede hacer con una sola tabla) podemos usar las funciones de agregado (COUNT para contar registros, SUM para sumar valores de un campo, MAX para obtener el valor máximo del campo, AVG para obtener el valor promedio del campo...) en el SELECT y en el HAVING. Si sólo usamos una función de agreagado en el SELECT, no es obligatorio usar GROUP BY, pero si además de eso indicamos que se devuelva un campo, el SGBD no lo permitirá y devolverá un error. Así, podemos hacer: SELECT COUNT(*) FROM tabla pero no: SELECT COUNT(*), campo1 FROM tabla Es algo ilógico que se pueda usar una función de agregado sin agrupar, pero lo que ocurre es que por simplicidad los SGBD lo permiten para cuando se quiere obtener el número de registros, o el valor mayor de un campo, cosas bastante frecuentes. En estos casos es el propio SGBD el que se encarga de agrupar y generalmente de optimizar estas consultas. Pero cuidado con el parámetro que ponemos en las funciones de agregado, porque si indicamos un valor que sea nulo no se aplicará; y tened en cuenta que COUNT(*) es una excepción, pues * no es ningún campo o valor, pero los SGBD lo admiten como atajo para simplemente contar todos los registros de la consulta... no intentéis hacer SUM(*) porque eso fallará.

Filtrando los grupos con HAVING

Junto con las funciones de agregado es habitual usar el HAVING para obtener, de los grupos de registros devueltos, los que cumplan una o varias condiciones: obtener los clientes que han facturado más de 60000 euros en un año, los que han realizado más de 20 pedidos en un mes, los que su media de importe de pedidos es superior a 1000 euros... En el HAVING se puede poner cualquier tipo de condición, incluso las que pueden ir en el WHERE, pero es muy recomendable que si una condición puede ir en el WHERE vaya en el WHERE, pues esas condiciones se usan en la optimización para recorrer más rápido los registros implicados; el HAVING, debido al orden de procesamiento de una consulta, se ejecuta casi al final del todo. El orden de procesamiento de una consulta suele ser: se unen y filtran las tablas implicadas (mediante las condiciones WHERE y las ON de las JOIN), se aplica el GROUP BY si lo hubiera (en este punto algunos SGBD ya saben qué alias hemos asignado a los valores del SELECT y nos permiten usarlos) y el HAVING si también lo hubiera (algunos SGBD también permiten usar alias aquí) y finalmente se aplica el ORDER BY. Huelga decir que los optimizadores de consultas tienen mucho que decir en estos pasos, pues a veces no necesitan ordenar el resultado, otras veces tienen que repasar el resultado de nuevo para ordenarlo...

Ejemplo

Tabla clientes:

id_cliente	nombre
2	John
10	Edward
13	Rose

Ahora vamos a añadir el importe del pedido a la tabla de pedidos. El importe de un pedido es, generalmente, propiedad del pedido pues la suma de los productos que contiene podría variar y el precio del pedido no debería variar. Es discutible pues se puede argumentar que el cambio de precio de uno de los productos que pidió el cliente no debe implicar el cambio del precio del pedido, y también se puede argumentar que si se considera inviolable el precio de un pedido también lo tienen que ser los precios de los productos que contiene.

id_pedido	id_cliente	fecha	importe
1	2	2009-01-24 08:00:15	940.00
2	2	2009-01-24 09:22:08	100.00
3	10	2009-01-27 13:10:22	64.00
4	2	2009-01-27 17:54:15	85.00
5	10	2009-01-29 08:13:01	540.00
6	10	2009-01-29 10:28:57	50.00
7	10	2009-01-29 18:26:59	35.00
8	2	2009-01-30 12:30:47	1420.00
9	10	2009-01-30 12:31:18	680.00

SELECT c.id_cliente, COUNT(*) num_pedidos, SUM(p.importe) total 

FROM clientes c 

INNER JOIN pedidos p ON c.id_cliente = p.id_cliente 

GROUP BY c.id_cliente

id_cliente	num_pedidos	total
2	4	2545.00
10	5	1369.00

Como vemos, el cliente 2 tiene 4 pedidos y el 10 tiene 5. El 13 no aparece porque no tiene ningún pedido y hemos usado INNER JOIN, pero si hubieramos usado LEFT JOIN sí aparecería con num_pedidos a 0 (ojo, no NULL, pues es una función, no un campo). También hemos sacado, a la vez, la suma de importes de sus pedidos. Si queremos saber cuántos pedidos al día hace un cliente, y de cuánto es su pedido con importe más pequeño, hacemos (MySQL):

SELECT c.id_cliente, DATE_FORMAT(p.fecha, '%Y-%m-%d') dia, COUNT(*) num_pedidos, MIN(p.importe) AS minimo 

FROM clientes c 

INNER JOIN pedidos p ON c.id_cliente = p.id_cliente 

GROUP BY c.id_cliente, dia

id_cliente	dia	num_pedidos	minimo
2	2009-01-24	2	100.00
2	2009-01-27	1	85.00
10	2009-01-27	1	64.00
10	2009-01-29	3	35.00
2	2009-01-30	1	1420.00
10	2009-01-30	1	680.00

Consultando la BD: optimizar consultas

Sobre la optimización hay muchos matices, que dependen siempre del SGBD. Principalmente cada vez que lanzamos una consulta a la BD el SGBD usa el optimizador para satisfacerla lo más rápido posible. Si bien es cierto que en ocasiones consigue todo lo contrario: que se ejecute más lenta. Como esta información es muy específica para cada SGBD, intentaré dar los consejos más frecuentes para que las consultas vayan mejor, y luego ampliaré un poco con MySQL pues es un sistema que se deja optimizar muy bien (aunque sería preferible no tener que optimizarlo...).

Consejos

• La primera regla de oro es que el optimizador usará los índices para buscar los registros según los filtros que le hayamos dado en el WHERE o el ON de las JOINs. Así, buscar por un nombre de cliente implicará que no usará ningún índice si el campo nombre no es índice. Esto no tiene por qué ser lento, pero relativamente lo será porque si ese campo fuera un índice la búsqueda sería mucho más rápida. Algunos SGBD no tienen penalizaciones por indizar muchos campos de una tabla, pero en MySQL sí que la hay ya que el tamaño del índice en general crece haciendo que sea inmensamente mayor y por lo tanto se pierde la ventaja de un índice ligero. La gestión de los índices es todo un mundo para los administradores de BD, eso cualquier DBA de Oracle os lo puede decir; así que ante la duda es mejor que consultéis Internet o a vuestro admin a ver qué os puede recomendar.
• El tamaño de fila es un valor muy importante en las consultas pues, si no hay índice a usar, se recorrerá toda la fila para buscar los campos del WHERE y así filtrar los resultados. Este valor se puede calcular con los tipos de dato de la tabla, o consultando el esquema INFORMATION_SCHEMA que contiene información sobre los meta-datos de todas las BD a las que tenemos acceso. También suele haber herramientas tipo SHOW que pueden devolvernos esa información, todo es cuestión de buscar en la documentación del SGBD.
• Es importante que los campos clave externa estén indizados pues las JOIN los usan para realizar las uniones de registros con sus relacionados. Si tenemos en cuenta que por cada registro de una tabla primaria en una subordinada puede haber N registros relacionados, ralentizará mucho la consulta no tener un índice en ese campo referencial.
• Las subconsultas son lentas. Esto no es cierto al 100% pero mejor si creemos que sí, pues para las subconsultas los SGBD suelen hacer tablas temporales, en memoria o en disco según pueda o decida qué es lo mejor. No hay que subestimar al optomizador en estos casos pues suele ser muy listo a la hora de decidir el camino más rápido para seguir.
• ORDER BY y GROUP BY suelen ser enemigos. Ordenar un resultado que se ha agrupado es tarea que los SGBD suelen hacer por ellos mismos, de modo que al agrupar por un campo casi seguro que aprovecha para ordenarlo (ascendementemente) por él. Si intentamos ordenar el resultado por otro campo, MySQL por ejemplo tendrá que dar otra vuelta sobre el resultado para re-ordenarlo (apostad porque otros SGBD lo harán también). Asímismo, se suelen usar los índices para el orden del resultado e indicar un orden distinto suele suponer una penalización en el tiempo de respuesta. De modo que usad el ORDER BY con la cabeza y cuando sea necesario, porque a veces ordenamos sin necesidad.
• No uséis el HAVING como sustituto del WHERE. El HAVING puede servir de WHERE (pero no a la inversa), pero no significa que convenga suplantarlo pues recordad que el filtro HAVING se aplica una vez satisfecho todo lo demás de la consulta: aquí ya no valen las ventajas de los índices. Evidentemente si queremos agrupar por un campo y obtener sólo los grupos que tengan más de X elementos o que la suma de uno de sus campos sea menor que Y, no queda otra que usar el HAVING, pues en el tiempo del WHERE no hay manera de saber cuánto vale el resultado de una función de agregado.
• El orden de las tablas del JOIN sí es importante. Cuando relacionamos tablas en una consulta, ya sea con JOINs o mediante condiciones del WHERE, es cierto que los optimizadores las unirán en el orden que ellos crean conveniente, pero a veces se equivocan. Sobre todo MySQL, que parece que cuando usamos JOIN su orden de unión es el que le damos... pero tenemos varias herramientas para solventar este problema: el consejo general es indicar los JOIN en el orden más lógico que se nos ocurra, y si aún así el optimizador lo cambia, el SGBD nos suele proporcionar herramientas para indicar en la consulta que se use el índice que le digamos o se use el orden de unión que explícitamente le indiquemos.
• Es un buen consejo separar los campos consultados con frecuencia de los campos grandes apenas usados. A veces tenemos en la tabla de clientes un montón de información que no se usa casi nunca, pero resulta que esa tabla de clientes es usada con frecuencia. Cuando las consultas no usen un índice la penalización viene dada por el tamaño de fila así que tener filas más pequeñas y cortas nos ahorraría mucho tiempo de respuesta. La solución es dividir la tabla en dos, con los campos más frecuenemente consultados en una tabla y los demás en otra (los TEXT, BLOB, y grupos de un muchos campos tipo CHAR/VARCHAR); para esto en la tabla que apenas usaremos nos llevaremos el id de la otra y lo dejaremos como clave primaria y foránea (externa) a su vez, en una relación 1 a 1 donde la usada con frecuencia sera la fuerte y la otra la subordinada.
• El tamaño de fila del índice mejor si es constante. Tener campos de tamaño variable, como VARCHAR, en un índice no suele ser buena idea si usas MySQL (sobre todo con el motor MyISAM) por cuestiones de rendimiento e incluso de consistencia. No sería la primera vez en la que yo haya visto que una tabla se corrompe por estas cosas... podemos echarle la culpa al SGBD, al sistema de ficheros del sistema o a lo que sea, pero es mejor que lo tengáis en cuenta sobre todo si se rompe con frecuencia una tabla (en mi caso cambiar el engine a InnoDB era posible y solucionó el problema).
• Las transacciones seguras masivas son más rápidas que las no seguras. Es habitual que tengamos que hacer cambios masivos en la BD, y especialmente notaremos una buena lentitud cuando sean inserciones. Los SGBD rara vez nos permiten usar distintos motores de almacenamiento de los datos, pero MySQL es un servidor que sí nos da a elegir. Es bien sabido que una inserción masiva es más rápida en MyISAM que en InnoDB, y la razón es la característica de transacciones seguras de InnoDB. MyISAM en contra bloquea las tablas, que es en cierto modo peor si queremos que la tabla siga en uso mientras insertamos, pero la gran demora de InnoDB puede ser un problema... si no usamos transacciones seguras. Un buen truco es iniciar la transacción segura, START TRANSACTION, realizar los INSERT, y luego finalizar la transacción segura con COMMIT. Sinceramente no recuerdo si esto es aplicable a otros SGBD como SQL Server u Oracle, pero si os va lento podéis probarlo.

Optimizar gracias a EXPLAIN (MySQL)

En MySQL tenéis una herramienta muy útil para la depuración de consultas: EXPLAIN. Anteponiendo EXPLIAN a la consulta (EXPLAIN SELECT ... FROM tabla WHERE ...) obtendremos un detalle de la ejecución de ella, con datos valiosos como el índice que se usa para cada JOIN, si se realiza un escaneo completo de la tabla, si el sistema tiene que volver a recorrer el resultado para ordenar... es un tema muy amplio para explicarlo en este artículo (además de que es algo exclusivo de MySQL) así que conviene echarle un ojo a la documentación oficial para que os hagáis una idea de cómo interpretar el resultado de EXPLAIN. Cuando depuréis una consulta con EXPLAIN, a grandes rasgos, lo más importante es fijarse en la columna "type" que determina el tipo de JOIN que realiza el SGBD: "all" significaría un escaneo completo de la tabla, "const" que sólo hay un resultado pues se está filtrando por una clave única comparada con un valor constante, "eq_ref" cuando es una JOIN de tablas relacionadas 1 a N, "ref" cuando la JOIN es de tablas N a M... conviene estudiarlo porque si en una JOIN que une dos tablas el "type" es "all" debemos eperar un resultado lento. Es cierto que en toda consulta con JOINs alguna de las tablas deberá ser la primera en leerse y si no se usan filtros de índice en el WHERE obtendremos un "all", pero quitando ese caso particular en ningún otra JOIN deberíamos tener algo que no sea "ref" o "eq_ref". Al fin y al cabo esto es lo que explicábamos más atras: si no se usan índices referenciales para satisfacer las JOINs se realiza el producto cartesiano de las tablas con su consecuente consumo de CPU y memoria.

Detectando las consultas lentas

Por lo demás, cuando nuestras aplicaciones vayan muy lentas y detectemos que es por las consultas a la BD, debemos encontrar las consultas que las ralentizan. MySQL tiene el slow_query_log que una vez habilitado en el servidor almacenará todas las consultas que tarden en ejecutarse más del tiempo que le especifiquemos (un segundo es muy lento, pero medio también en una consulta muy frecuente). Una vez que sepamos las consultas podemos revisar los consejos de arriba para ver si estamos haciendo algo mejorable, o quizá tengamos un mal diseño de la BD. También, en ocasiones, nos podemos encontrar que se nos ha olvidado declarar un índice, y eso tiene una solución muy fácil. Porque decir que la BD, o una tabla, se nos ha quedado poequeña, es algo muy poco probable: mejor revisar el diseño y el uso que le damos porque dándole una vuelta podemos ver fallos en el diseño y mejorar sobremanera el rendimiento. Si la búsqueda de las consultas lentas se hace muy complicada, siempre podremos buscar algún monitor específico para nuestro SGBD (en esto el administrador de la BD nos puede ayudar mucho: le conviene) pues estas herramientas suelen proporcionar información vital para hallar este tipo de problemas.

---