Click here to translate this post

Optimizar MySQL puede resultar muy importante si disponemos de una BBDD en donde la cantidad de registros va aumentando considerablemente. Pongamos por ejemplo una página web donde tenemos un script que guarda en una tabla de MySQL información de cada visitante.

La tabla es muy sencilla:

create table visitas(id int(9) auto_increment primary key, ip varchar(15), fecha = datetime);

El script se encarga de asegurarse de únicamente insertar un nuevo registro en la tabla en el caso de que esta sea la primera visita del usuario en las pasadas 24 horas. Para lograrlo primero realizamos el siguiente SELECT:

SELECT COUNT(*) FROM visitas WHERE ip = ‘xxx.xxx.xxx.xxx’ AND fecha BETWEEN ‘YYYY-MM-DD 00:00:00′ AND ‘YYYY-MM-DD 23:59:59′;

Si el query nos devuelve 1 significa que este visitante ha accedido a la web hace menos de 24 horas, por lo que no insertamos un nuevo registro.

Si el query nos devuelve 0 procedemos a realizar un INSERT con la información del visitante.

INSERT INTO visitas SET ip = ‘xxx.xxx.xxx.xxx’ AND fecha = NOW();

Este código y tabla de MySQL funcionan bien siempre y cuando tengamos una web con pocas visitas. El SELECT para comprobar si el usuario ha visitado la web recientemente se ejecuta en una media de 0.00 sec si tenemos pocos registros. ¿Qué sucede si las visitas a nuestra web aumentan y tenemos miles de usuarios conectándose diriamente? Nuestra tabla visitas pasa de tener unos pocos miles de registros a millones. Si realizamos el mismo select en una tabla con 2 millones de registros nuestros tiempos de espera aumentan a cifras inaceptables.

mysql> SELECT COUNT(*) FROM visitas WHERE ip = ‘xxx.xxx.xxx.xxx’ AND fecha BETWEEN ‘YYYY-MM-DD 00:00:00′ AND ‘YYYY-MM-DD 23:59:59′;
+———-+
| COUNT(*) |
+———-+
|        0        |
+———-+
1 row in set (4.45 sec)

Si por cada visita MySQL tarda 4.45 segundos en devolver el resultado el servidor quedará totalmente colapsado.

La solución a este problema es bien simple. Tan sólo debemos de hacer uso de índices (Indexes). Gracias a los índices MySQL no tiene que realizar la búsqueda desde el primer a último registro.

Crearemos un índice de la columna ip.

mysql> CREATE INDEX ip ON visitas (ip);
Query OK, 2008402 rows affected (53.75 sec)
Records: 2008402  Duplicates: 0  Warnings: 0

Si ahora realizamos el anterior SELECT, que sin el índice tardó 4.45 en devolver el resultado, comprobamos que lo devuelve en 0.03 segundos.

mysql> SELECT COUNT(*) FROM visitas WHERE ip = ‘xxx.xxx.xxx.xxx’ AND fecha BETWEEN ‘YYYY-MM-DD 00:00:00′ AND ‘YYYY-MM-DD 23:59:59′;
+———-+
| COUNT(*) |
+———-+
|        0        |
+———-+
1 row in set (0.03 sec)

El uso de índices es sumamente importante para optimizar una BBDD MySQL. Es una buena idea comprobar que ningún SELECT de vuestra web/aplicación tarda demasiado en devolver los resultados. Si así es, lo más probable es que no disponga de ningún índice.

Para más información: Web oficial MySQL sobre índices
Follow me on Twitter