Usando EXPLAIN para analizar consultas en MySQL

El uso de la herramienta EXPLAIN en MySQL es muy simple:

mysql> explain select * from tabla_x;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   |type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tabla_x | ALL  | NULL          | NULL | NULL    | NULL | 1002 |       | 
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

Aquí podemos analizar algunos datos con respecto a nuestra consulta para optimizar nuestros queries sean simples, compuestos (digase con JOIN's y así) o muy complejos xD .

Entre algunas cosas que podemos observar es si usa llaves o indices, el numero de filas que estaría leyendo de la base de datos esa consulta. Por ejemplo un valor que deberíamos evitar en nuestras consultas es el campo 'type' con 'ALL' esto quiere decir que está leyendo la fila completa y no se remite a un campo en particular o a una llave en un índice, por lo tanto la consulta será más lenta que si hacemos uso de alguna de estas alternativas de optimización.

En esta página encontré varios ejemplos bastante ilustrativos y una explicación de cada campo del comando EXPLAIN que pueden consultar para más detalle:

Aquí

También pueden acceder a la ayuda de MySQL en internet.

¡Mucha suerte con sus consultas complejas! la necesitarán ;)

¡Saludos javer@s!

Acelerar importación de Base de Datos MySQL desde consola

HOla!
Esta ocasión platico lo que me pasó. Tratando de cargar una base de datos bastante grande (una tabla cuyo sql era de aprox 6GB), me encontré con la dificultad de que al importarlo a través de la linea de comandos de MySQL tardaba un demonial, más de lo que podría suponerse, tardando 20 segundos o más por cada 1000 registros (hablábamos de unos 5 millones de registros).
Me di cuenta que el dump lo habían generado desde una útil herramienta llamada SQLyog y que mi compañero desde su máquina haciendo el import con la misma herramienta tardaba muuuucho menos que yo.
Entonces vi que el archivo sql del dump no contenía ninguna clase de comentario ni directiva extra más que la carga de los datos, es decir, el create de la tabla y los inserts, ¡Ah claro! y que había sido hecho por SQLyog.

Encontré una página con algunas estrategias para acelerar los procesos de dump y de importación:

Aquí

Entre ellos uno me llamó la atención y parece la solución a mi problema. Al restaurar desde consola en linux sería bueno activar las directivas para cancelar varios chequeos que hace MySQL y acelerar el proceso.

(
    echo "SET AUTOCOMMIT=0;"
    echo "SET UNIQUE_CHECKS=0;"
    echo "SET FOREIGN_KEY_CHECKS=0;"
    cat dump.sql
    echo "SET FOREIGN_KEY_CHECKS=1;"
    echo "SET UNIQUE_CHECKS=1;"
    echo "SET AUTOCOMMIT=1;"
    echo "COMMIT;"
) | mysql -u... -p... target_database

o podemos crear un archivo para ejecutar desde el shell:

#!/bin/bash
MYSQL_USER="..."
MYSQL_PASSWORD="..."

function restore() {
    echo $1;
    (
        echo "SET AUTOCOMMIT=0;"
        echo "SET UNIQUE_CHECKS=0;"
        echo "SET FOREIGN_KEY_CHECKS=0;"
        cat "$1.sql"
        echo "SET FOREIGN_KEY_CHECKS=1;"
        echo "SET UNIQUE_CHECKS=1;"
        echo "SET AUTOCOMMIT=1;"
        echo "COMMIT;"
    ) | mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$1"
}

Con esto aceleramos mucho la inserción, sin embargo de todos modos tarda.

En la página donde saqué estas cosas también vienen otras recomendaciones que pueden ser bastante útiles al exportar e importar Bases de Datos en MySQL.

"Fatal error: Maximum execution time of 300 seconds exceeded" al cagar archivo grande en MySQL

Clásico error  si trabajamos transfiriendo o cargando scripts de bases de datos muy grandes. Los valores en un servidor MySQL por default -- sobre todo si es una máquina de escritorio, laptop o similares de computadoras personales -- suelen estar orientados a requerimientos muy básicos.

Hay varios parámetros que se pueden adecuar en los valores de nuestro servidor MySQL los cuales los podemos cambiar (en mi caso usando ubuntu) en /etc/mysql/my.cnf .

Sin embargo es raro que aparezca el error que arriba comento pero al cargar un archivo dump de MySQL desde consola, ¡¿¿en que endemoniado momento se metió PHP en esto??!

Me sucedió a mi, y por más que cambié los parámetros de MySQL e incluso de PHP (php.ini) no se solucionaba eso. Cargué desde consola, desde el prompt del cliente de MySQL y nada. La línea completa del error al cargar el archivo (que era un archivo grande, alrededor de 500MB, pero vamos, ¡no es para tanto!) era la siguiente:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Fatal error: Maximum execution time of 300 seconds exceeded in /usr/share/phpmya' at line 57

Era un error de MySQL que involucraba extrañamente a PHP. Desesperado fui al archivo en cuestión y en la última linea aparecía el texto tal cual: "Fatal error: Maximum execution time of 300 seconds exceeded in /usr/share/phpmyadmin/libraries/export/sql.php on line 1080" . Pero para llegar a esta solución hubo que batallar un buen rato, todos los blogs halaban de cambiar los parametros de PHP, si como no... 

¡PERO SI CLARO!

Había ocurrido un error al crear el archivo dump de la base de datos en el servidor origen y se había imprimido la salida del mismo en el archivo al final, por supuesto.

Entonces volvemos a crear el dump asegurándonos que se genere correctamente (Este error apareció al intentar hacer un dump de una base muy grande a través de PhpMyAdmin, les recomiendo usen el mysqldump desde consola).

Y listo, no debe haber mayor problema al cargar su archivo correcto.. jeje..

Aquí donde encontré la inspiración para la solución a este problema (le pasó lo mismo que a mi). Thanks amigo!

Entrada

Hasta la vista javer@s

Case insensitive Mysql: sensibilidad a mayúsculas y minúsculas en MySQL

A continuación incluyo un pequeño artículo donde habla de como MySQL es sensible a mayúsculas y minúsculas en función del sistema operativo que lo esté conteniendo. Como es bien sabido Linux es sensible a mayúsuculas, por lo tanto en un servidor MySQL corriendo en Linux se presentará éste caso. En uno corriendo en Windows al parecer no será así, porque Windows no es sensible a mayúsculas. En el artículo se da una breve descripción del porqué. 

Tuve un problema similar al del artículo utilizando Hibernate con anotaciones en una aplicación Java, donde se nos había ocurrido poner los nombres de tablas con mayúsucula inicial, como por ejemplo: "Usuarios", quienes usábamos Linux tuvimos que resolverlo porque nos creaba por ejemplo la tabla "usuarios" vacía. 

Me voy directo a la manera para resolverlo, transcribiendo del artículo:

Afortunadamente, MySQL contempla el problema. Basta arrancar con mysqld –lower_case_table_names=1 y así cuando creemos cualquier base de datos o tabla, automáticamente la pondrá en minúsculas, haciendo que aparentemente se convierta en "case insensitive". No es totalmente cierto, porque lo que hace en realidad es convertirlo todo a minúsculas.

Aquí el artículo completo. Espero les sea de utilidad:

MySQL: Bases de datos y tablas “case insensitive”

Aprovecho para agradecer al autor, muy recomendable su blog. 

¡Saludos Javer@s!