FreeBSD 7.X: Indices en Mysql 5.1.x

Tenemos la siguiente tabla: Tres.

mysql: DESCRIBE Tres;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| StatusID | int(11) | NO | PRI | NULL | auto_increment |
| SOID | int(6) | NO | PRI | NULL | |
| IP_ADDR | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+

Ahora vamos a ver los indices:

mysql> SHOW INDEXES FROM Tres;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Tres | 0 | PRIMARY | 1 | Status_ID | A | 12 | NULL | NULL | | BTREE | |
| Tres | 1 | SOID | 1 | SOID | A | NULL | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Tenemos por indice a el Status_ID es una tipo de dato int de 6 digitos, y por default es indice.
Despues vemos a SOID varchar de 6 caracteres, no es Key pero es indice, este valor es nuestra clave, ya que nosotros vamos a hacer busquedas basadas en este campo.

Y ademas no es unico, es un dato que se puede repetir mas de 1 vez.

Ya hemos capturado varios registros, vamos a verlos:

mysql> select * from Tres;
+-----------+-------+--------------+
| Status_ID | SOID | IP_ADDR |
+-----------+-------+--------------+
| 1 | 20000 | 192.168.1.1 |
| 2 | 20001 | 192.168.1.2 |
| 3 | 20002 | 192.168.1.3 |
| 4 | 20003 | 192.168.1.4 |
| 5 | 20004 | 192.168.1.5 |
| 6 | 20005 | 192.168.1.6 |
| 7 | 20006 | 192.168.1.7 |
| 8 | 20001 | 192.168.1.8 |
| 9 | 20002 | 192.168.1.9 |
| 10 | 20003 | 192.168.1.10 |
| 11 | 20001 | 192.168.1.11 |
| 12 | 20002 | 192.168.1.12 |
+-----------+-------+--------------+
12 rows in set (0.00 sec)

Tenemos que varios SOID se han repetido, vamos a ver si nuestros indices estan funcionando:

mysql> explain select * from Tres where SOID=20001;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | Tres | ALL | SOID | NULL | NULL | NULL | 12 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Podemos ver que no, ya que la tabla en total tiene hasta ahorita 12 registros, y cuando hacemos esta busqueda usando"WHERE" el motor de mysql esta haciendo un barrido total a la tabla, por lo tanto no esta usando nuestros indices, sabiendo que SOID es un indice.

El problema radica en que como SOID es un tipo varchar, debemos cerrar entre comillas el valor para que mysql lo tome como lo que es, si no lo hacemos como en el caso anterior, lo toma como dato numerico, por ello falla.

Ahora vamos a ver la diferencia:

mysql> EXPLAIN select * from Tres where SOID="20001";
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | Tres | ref | SOID | SOID | 8 | const | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Vean, ahora si los uso, ya que solo nos estra regresando los 3 registros que tiene con ese valor, y este dato lo esta sacando de los indices, ya no tuvo que hacer un barrido completo a la tabla.

Esto salva preciado CPU, acceso a disco que sumado es tiempo de respuesta muy importante cuando estamos hablando de tablas con mas de 100,000 registros.

Hasta la proxima!!!