Localizar y optimizar consultas SQL problemáticas es muy laborioso y complejo a veces. Uno de los mayores problemas con los que me he encontrado, es localizar exactamente desde dónde se ha ejecutado esa consulta para así reproducir el problema lo más fielmente posible en un entorno de desarrollo.

Un SHOW PROCESSLIST permite visualizar la lista de consultas que se están ejecutando, con su tiempo y el host desde donde se ha realizado la conexión pero sin más información, cuando lo realmente interesante es ¿desde dónde se ha ejecutado esta consulta?. La solución es muy sencilla: utilizar comentarios SQL dentro de las consultas.

Hoy en día, cualquier aplicación mínimamente grande utiliza una capa de abstracción (bien integrada en un framework, bien propia) por lo que no suele resultar difícil interceptar las consultas para añadir los comentarios. Si combinamos esto con funciones como debug_backtrace() de PHP (o su equivalente en Ruby o Python), tendremos una valiosísima información, ya que es posible determinar desde qué función, clase+metodo e incluso la línea concreta:

SELECT /* controlador test, metodo database, linea 17 */ field1, field2 FROM table

Este pequeño truco faciltará mucho el trabajo de detectar consultas conflictivas especialmente en casos en los que se ejecutan desde muchos lugares en diferentes archivos, y a efectos prácticos el rendimiento no se ve mermado.

Hay proyectos en los que es necesario pensar en la portabilidad de la base de datos, permitiendo que funcione perfectamente en diferentes servidores. Por ejemplo, el famoso phpBB soporta 7 sistemas. Esto no es recomendable en proyectos en los que el rendimiento es algo vital como sitios web y sólo debe aplicarse a proyectos donde prima la portabilidad.

A nivel de aplicación, esto requiere el uso de una capa de abstracción que hoy en día integran la mayoría de frameworks o que puede lograrse con con la extensión PDO de PHP o clases como ADOdb. Y para convertir las bases de datos entre sí, ya he hablado de SQL::Translator, un potente script programado en Perl.

Conoce el estándar SQL-92

En la especificación de SQL-92 se definen los puntos comunes del lenguaje. Tenerlos claros los puntos es primordial como por ejemplo el uso de comillas simples para literales.

Comprueba todos los datos antes de enviarlos a la base de datos

Esto debe ser básico en cualquier desarrollo, pero nunca sobra recordarlo. Todos los datos que se almacenen deben estar comprobados, ya que un sistema puede aceptar un valor no válido para un tipo de dato y convertirlo mientras que otro sistema lo rechazará.

Usa correctamente los valores NULL y asigna valores por defecto

Todos los campos que sean omitibles en un INSERT o un UPDATE deben tener un valor por defecto o bien ser NULL. En bases de datos como SQLite, el no especificar un valor por defecto provoca un error, mientras que en MySQL no.

Usa transacciones cuando se requiera

Cuando se realizan varias consultas seguidas de inserción, actualización o borrado, es recomendable el uso de transacciones. Es mejor no realizar ningún cambio a que se realice a medias, perdiendo datos.

Usa consultas INSERT y UPDATE completas

Nunca deben omitirse partes de las sentencias de inserción y actualización. Por ejemplo: INSERT INTO tabla (campo1, campo2) VALUES (’a', ‘b’).

Usa siempre ORDER BY

MySQL permite establecer el orden por defecto de una tabla y en otros sistemas el orden puede variar por cada consulta realizada, ya que se puede ordenar por fecha de actualización o inserción del registro. Por eso nunca debe omitirse ORDER BY.

Evita el uso de la integridad referencial

La integridad referencial no está soportada en todos los sistemas (MySQL con tablas InnoDB sí la soporta pero no con tablas MyISAM, SQLite permite asignar claves foráneas pero no hace nada con ellas), por lo que la lógica debe trasladarse a la programación, borrando o actualizando los datos relacionados de ser necesario.

Evita el uso de funciones propias

No deben usarse funciones propias de cada sistema, dificultan la portabilidad. Lo que se puede hacer con una función en una consulta puede hacerse con otra función en la programación. Eso sí, ciertas funciones de tratamiento de cadenas y fechas pueden ser comunes o muy similares, por lo que en caso de ser necesario, debe tenerse en cuenta a la hora de programar para usar la adecuada según el sistema. Algunas capas de abastracción como ADOdb integran ayudas para estos casos.

Evita el uso triggers y procedures

Todo lo que puede incluirse en disparadores y procedimientos almacenables puede incluirse en la lógica de la aplicación. Se pierde comodidad pero evitan problemas, ya que la implementación de estas funciones varía y ciertos sistemas no las implementan.

Evita el uso de vistas

Las vistas funcionan de diferente forma entre los diferentes RDBMS existentes, sobre todo en la actualización de los datos que consulta, lo que puede provocar resultados inesperados. Hay que recordar que sólo algunos sistemas permiten la actualización de datos en vistas, por lo que es importante no hacer uso de esa funcionalidad.

Evita el uso de subconsultas

Del mismo modo que las vistas, las subconsultas funcionan de diferente forma entre los diferentes RDBMS existentes y no todos las implementan. Cada sistema tiene sus restricciones, por lo que una subconsulta que funciona perfectamente en

Evita el uso tipos de datos y atributos no estándar

Los tipos de datos específicos de ciertos motores deben evitarse. Por ejemplo, una fecha timestamp puede almacenarse en un campo INT de 32 bits, pero en MySQL puede almacenarse en un campo tipo TIMESTAMP con el atributo ON UPDATE CURRENT_TIMESTAMP que no es extensible a otros sistemas. Otro ejemplo pueden ser los tipos BOOL, que pueden sustituirse por enteros (0 – 1).

Mi compañero Luis nos indicaba en Sentido Web cómo simular el rownum de Oracle en MySQL:

SELECT tabla.*, @rownum:=@rownum+1 AS 'rownum'
FROM tabla, (SELECT @rownum:=0) r

El problema aparece cuando se quiere hacer un UPDATE asignando el valor de rownum a un campo cuando se necesita un ORDER BY, ya que MySQL no permite usar orden en las consultas de actualización cuando se usan 2 tablas.

La solución pasa por definir previamente una variable que se vaya actualizando para cada fila para la que se realize el UPDATE:

SET @rownum=0;
UPDATE tabla SET campo1=@rownum:=@rownum+1 ORDER BY campo2

No le había prestado demasiada atención a Eclipse hasta la salida de PHP IDE ( había probado los plugins PHPEclipse y Xored Trustudio con bastante decepción ), pero ahora empieza a ser rival digno para el gran Zend Studio.

El problema es que de serie no trae todo lo deseable. El paquete Web Tools Platform trae editor HTML, Javascript, CSS, JSP y SQL, editor avanzado de XML y una interesante herramienta para consultas a bases de datos ( al estilo Zend Studio ). Pero a mi me faltaban algunas cosillas:

• Soporte para Subversion: Subclipse
• Soporte para SFTP: Sftp File Synchronization
• Editor de JavaScript avanzado: JSEclipse. Éste es especialmente interesante porque tiene por fin un sistema decente de escritura predictiva y ayudas incluso con soporte para Prototype.
• Accesos y consultas a bases de datos: QuantumDB