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+

zp8497586rq

jfdghjhthit45

También te puede interesar

12 Comments

  1. de la red – 31/03/2010 « Tecnologías y su contexto
    mar 31, 2010 @ 18:23:08

    [...] 10 consejos SQL para acelerar tu Base de datos [...]

  2. Tips para acelerar bases de datos SQL
    abr 02, 2010 @ 12:52:13

    [...] el blog de craftyman ha publicado hace unos días unos tips que te ayudarán a mejorar el rendimiento de cualquier [...]

  3. Linux Hispano | Lo mejor de mi RSS del 29 de marzo al 4 de abril
    abr 05, 2010 @ 01:24:19

    [...] 10 consejos SQL para acelerar tu Base de datos – Craftyman ¿Te ha gustado?: [...]

  4. 10 consejos para optimizar el uso de MySQL - VitaminaWEB.com
    abr 08, 2010 @ 06:56:38

    [...] tednologia.com Sin [...]

  5. 10 consejos SQL para acelerar tu Base de datos - DbRunas
    abr 08, 2010 @ 13:20:47

  6. Sin tiempo para escribir.54
    abr 20, 2010 @ 03:47:45

    [...] 10 consejos SQL para acelerar tu Base de datos. vía: craftyman [...]

  7. Consejos SQL para acelerar tu Base de Datos
    abr 21, 2010 @ 22:36:44

    [...] La verdad todas estas practicas por sencillas que parezcan son muy útiles para el aceleramiento y ahorrar recursos de nuestro Servidor, Recuerden siempre hay que regresar a las bases , Pueden ver mas consejos de como optimizar su base de datos en http://tednologia.com [...]

  8. Compilado de Articulos (PARTE 2) « Argentina Geek 2.0
    may 16, 2010 @ 19:47:26

    [...] 10 consejos SQL para acelerar tu Base de datos – Craftyman [...]

  9. Javier
    jun 22, 2010 @ 12:53:56

    No entiendo el punto 1: que tiene que ver usar un criterio de fecha sobre un campo de tipo fecha de una tabla con el hecho de usar el cache de consultas SQL?

    Por lo demas, muy buenos tips…

  10. craftyman
    jun 22, 2010 @ 22:19:15

    Hola Javier, respondiendo a tu pregunta sobre en punto numero 1:

    MySQL tiene integrado su propio sistema de cache, esto lo procesa internamente, el problema es cuando utilizamos una función MySQL dentro de un SELECT, al hacer esto ya no estamos beneficiados por el cache de MySQL, ya que tiene que procesar la función puesta en la consulta, es por eso que en mi ejemplo muestro una manera de aprovechar PHP para generar una fecha actual y tener el beneficio del cache de MYSQL, el ejemplo común que se utilizaría para una consulta sin cache seria:

    $ stmt = ” SELECT * FROM usuario donde signup_date > = NOW () ” ;

    Espero haber servido de algo.

    Este articulo explica algunos parametros para monitorear el cache en nuestro server de DB.

    http://bit.ly/aaVztU

    Saludos cordiales!

  11. José
    jun 24, 2010 @ 15:00:59

    Buenas,

    buen post, pero está copiado de http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/.

    Al menos cita la fuente, que has usado hasta las imágenes sin citarla.

    Saludos.

    • craftyman
      jun 24, 2010 @ 23:53:12

      Que tal José,

      Gracias por el detalle, se ha debido pasar, pero ya actualice el POST.

      Saludos cordiales.