FoxPress – Julio-Agosto 2006

Algunos puntos para optimizar MySQL

http://www.fpress.com/

Por la Redacción de FoxPress

· MySQL se interpreta de la derecha a la izquierda por eso deberías poner los delimitadores más significativos tan lejos de la derecha como sea posible.

· Traete sólo los campos que necesites, evita usar Select *

· No pongas las cosas que cambian muy raramente en la Base de Datos, en su lugar ponlos en algún tipo de array que lea de un archivo Include.

· Usa índices en las columnas que usen el WHERE y en las columnas que utilices en el ORDER BY.

· Los índices son muy interesantes en las busquedas pero ralentizan los insertados de datos.

· Usa la instrucción EXPLAIN para analizar tus índices.

· Si quieres sólo una línea como resultado de tu consulta deberías especificar LIMIT 1. De esta forma MySQL no continua buscando.

· Si usas $line = mysql_fetch_array($result) tendrás dos formas de acceder a las columnas, $line[0] y $line['columnname']. Si usas unicamente $line['columnname'] deberías usar $line = mysql_fetch_assoc($result) ya que ahí no existe ningun array $line[int index].

· Algunas veces mysql_free_result() consume más memoria que la que ahorra. Comprueba las diferencias con memory_get_usage().

· No preguntes a la base de datos por los mismos temas una y otra vez, guárdate esos resultados.

· Usa NOT NULL como valor por defecto siempre que puedas pues acelera la ejecución y te aborra un bit como valor por defecto.

· Usa tipos de datos que se adapten a tus datos, no demasiado grandes. Por ejemplo, INT puede tomar valores hasta 4294967295, que con frecuencia es demasiado grande. Usa MEDIUMINT o SMALLINT donde se pueda aplicar.

· Usa valores por defecto e inserta sólo aquellos que difieren. Los valores por defecto se insertan mucho más rápido.

· Cuando ejecutamos una instrucción DELETE sobre una tabla de nuestra base de datos, las entradas borradas, no desaparecen fisicamente, sino que solo desaparecen sus valores, es decir, quedan espacios vacios en nuestra base de datos. Con el tiempo, podemos encontrarnos con que nuestra base de datos, contiene gran cantidad de regístros vacíos que ocupan espacio y relentizan nuestra base de datos y que no tienen ninguna función, para evitar eso, MySQL dispone de una función que optimiza una tabla eliminando todos esos espacios:

OPTIMIZE TABLE nombre_de_la_tabla

Pero posiblemente nos convenga optimizar una base de datos entera, para lo que usaremos el siguiente script:

<?
// CONFIGURAR EL ARCHIVO
$direccion = "localhost";
$usuario = "tu_nombre_de_suario";
$pasword = "tu_pasword";
$nombre_db = "tu_base_de_datos";

// CODIGO DE OPTIMIZACION
$db = mysql_connect($direccion,$usuario,$pasword);
$tablas = "SHOW TABLES";
$tablas = mysql_db_query($nombre_db,$tablas,$db);
while ($tabla=mysql_fetch_array($tablas)) {
$optimizar = "OPTIMIZE TABLE ".$tabla[0];
mysql_db_query($nombre_db,$optimizar,$db);
}
?>

Como vemos, una vez configurado, el script ejecuta la instruccion SHOW TABLES para conseguir una lista de todas las tablas y optimizarlas una a una gracias a un ciclo while.

EXPLAIN muestra (explica!) como se usan tus consultas. Poniéndolo antes de un SELECT, puedes ver si los índices se están usando propiamente, y qué tipo de join se está realizando...

Por ejemplo: EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

 
+----------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL | NULL | NULL | NULL | NULL | 2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

¿Qué nos dice esto?

· table nos dice sobre qué tabla estamos trabajando

· type nos dice qué tipo de Join se está usando. De peor a mejor son: system, const, eq_ref, ref, range, index, all

· possible_keys Muestra qué posibles índices se pueden aplicar a esta tabla.

· key Qué indice lo que se está usando ahora mismo

· key_len nos dice el tamaño del índice. Cuanto más corto mejor.

· ref Nos dice qué columna se está usando

· rows Número de registros que MySQL piensa que se deberían examinar para obtener los datos.

· extra Extra info – Lo malo sería que pusiera "using temporary" y "using filesort"

Esta consulta es la peor de las peores ya que no existen claves para poder usar, así MySQL tiene que ir a través de todos los registros (sólo 2 en este ejemplo, pero imagina una tabla más grande).

Ahora podemos indexarla y si volvemos a ejecutar el EXPLAIN, obtenemos:

+----------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| employee | const | PRIMARY | PRIMARY | 10 | const | 1 | |

+----------+-------+---------------+---------+---------+-------+------+-------+

FoxPress – Julio-Agosto de 2006