Os reporto algunos consejos para optimizar nuestras consultas select de bases de datos MySQL.
- Para que exista una buena optimización de las consultas, es fundamental partir de una buena base. Y que mejor base para una base de datos (valga la redundancia) que diseñar un modelo entidad relación para transformarlo posteriormente en un modelo relacional, y ya de este último obtener la base de datos SQL (LDD, LMD).
- INDEXAR: Es quizás el apartado más importante, es primordial que nuestro modelo E/R > Relacional sea correcto, para deducir cuales serán las columnas de clave primaria y foránea que el gestor tomará como índices de consultas.
- No menospreciar el valor de las claves foráneas, son de mucha importancia para optimización de consultas y relaciones.
- Elegir el motor de almacenamiento adecuado (tipo de tabla): innoDB, MyISAM, etc, todo dependerá de lo que busquemos y en función de esto podemos optimizar bastante nuestra base de datos.
- Las tablas con row_format, de tipo fixed son más rápidas en lectura que las dynamic. Este punto esta íntegramente relacionado con el punto 9.
- En los select, solo se deben extraer los datos que necesitemos, cuanto menos empleo de “ * ”, mejor.
- manu ha dado con un tip muy certero (en los comentarios), y es que si trabajamos con PHP y MySQL se debe usar siempre un SELECT COUNT(*) antes que un mysql_num_rows para contar los resultados.
- No abusar del empleo de like. Si obligas al gestor de base de datos a examinar campos blob, text, la velocidad de la consulta descenderá notoriamente.
- Los datos numéricos son lógicamente examinados y extraídos más rápido que los datos textuales.
- Los campos Not Null agilizan la lectura, por tanto no se deben usar de forma desproporcionada los valores nulos.
- Usar lo menos posibles group by y order by.
- Los campos de longitud fija char son más rápidos en lectura (aunque mayores en peso), que los de longitud variable varchar.
- Para las consultas multi-tabla (con relaciones), es preferible el empleo de uniones por medio de inner join en el from, que establecer uniones de índices como condiciones del where.
- El orden de las cosas importa. Cuesta un poco pero es bueno reflexionar y buscar una consulta que basada en la lógica extraiga los datos recorriendo el menor número de posibilidades.
- Probar nuestras consultas con EXPLAIN. Sobre este asunto hay información muy valiosa en MySQL Hispano (explain).
- Usar datos reales, o que se aproximen. Este punto es también muy importante, me ha pasado de darle mil vueltas a una consulta (con explain) y ver porque no acababa de ser optimizada. Finalmente descubrí que el gestor en determinados casos prefiere leer TODO a usar índices, creyendo (acertadamente) que leyendo todo resolverá la situación antes que recuperando los índices. Esto sucede por ejemplo cuando en una tabla tenemos menos de 8 tuplas (filas).
El gestor incorpora un optimizador de consulta (muy muy bueno), que se encarga de analizar la consulta que has enviado para trasformarla en otra, de forma que se procese lo más rápido posible. Al final la idea es hacer trabajar poco a este optimizador.
