10 consejos SQL para acelerar tu Base de datos

La mayoría de sitios en la Web depende de una o varias Bases de datos, ya sean para consultar, ingresar, borrar registros. Y en todas es necesaria la optimización para el aceleramiento y ahorrar recursos de nuestro Servidor, este articulo trae 10 consejos muy útiles para optimizar y acelerar nuestras bases de datos.

1. Optimizar las consultas con el Cache de Consultas SQL

Utilizando la función date de PHP podemos consultar en el cache de la base de datos:

$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. No olvidar agregar índices a los campos

Esto acelerara considerablemente sus consultas a la base de datos, para ver si olvido poner índice en algunos campos puede utilizar EXPLAIN, colocándolo delante de la consulta SQL.

EXPLAIN SELECT ...
FROM usuarios u
JOIN comentarios c ON u.id=c.usuario_id

3. Utilizar LIMIT 1 para obtener una única fila

Cuando sabemos que nuestra consulta nos devolverá solo un registro, por ejemplo al consultar por un id de usuario, es recomendable utilizar LIMIT 1, esto hará que cuando el registro sea encontrado, ya no recorrerá las demás filas para comparar.

$rs = mysql_query("SELECT 1 FROM user WHERE id = '1' LIMIT 1");

4. Agregar índice a los campos de búsqueda

Al tener en una tabla, campos con los que hacemos búsqueda, es bueno que esos campos sean índices para acelerar la consulta.

5. Utilizar el mismo tipo de columnas para los JOIN

Cuando relacionamos tablas tenemos que agregar índices, luego de esto asegurarnos que los dos campos tengan el mismo tipo de dato (DECIMAL, INT, etc.). Ya que esto afectara a procesos y operaciones internas de MySQL.

 $r = mysql_query("SELECT company_name FROM users
     LEFT JOIN companies ON (users.state = companies.state)
     WHERE users.id = $user_id");

6. No utilizar el ORDER BY RAND()

Si queremos sacar un registro de una tabla aleatoriamente, es mejor sacar el valor por código PHP, ya que al utilizar RAND() en MySQL estaremos llamando a la función por cada registro en la tabla para obtener un resultado.

// mal
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// bien
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. Evitar utilizar el SELECT *

Traer toda la data es casi siempre innecesario y hace procesar más al motor de base de datos, es muy recomendable cambiar el habitual * por los campos necesarios.

// mal
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// bien
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

8. Utilizar NOT NULL

A menos que tengas buenas razones para poner NULL a un campo, trata siempre de evitar esto y colocarlas con NOT NULL, ya que este tipo de columnas (NULL) requieren de un espacio adicional y de una complejidad interna en las sentencias de comparación

9. Guardar direcciones IP en campos UNSIGNED INT

Muchos de los desarrolladores (me incluyo) guardamos las direcciones IP en un campo VARCHAR sin saber que las podemos almacenar en campos INT que tiene solo 4 bytes de espacio, tenemos que asegurarnos que el campo sea UNSIGNED INT para aceptar valores más altos, ya que la dirección IP utiliza el rango de 32 bit.
Para transformar las IP a campo numérico y viceversa podemos utilizar funciones MySQL y PHP.

//mysql
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
//php
$ip_num = ip2long($_SERVER['REMOTE_ADDR']);
$ip_normal = long2ip($row['ip_address']);

10. Evitar ejecutar consultas dentro de bucles

Un claro ejemplo es al actualizar una serie de filas en la BD, esto se podría resumir en una sola consulta para optimizar recursos en nuestro servidor.
PHP

foreach  ($display_order  as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

MySQL

UPDATE  categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

Post Original: Nettuts+