como puedo optimizar una tabla con mucho registros

[jrsantos]

Les agradezco mucho a cada uno de ustedes sus aportes.

Ok voy explicar un poco mas claro el escenario donde estoy teniendo el problema.
haber si me explico mejor.

Tengo 3 tablas.

tb_grupos
tb_items
tb_estatus.


Cada elemento de la tabla tb_grupos tiene asociado un conjunto de elementos de la tabla tb_items la cardinalidad es de 1:1 , cada item tienen un numero unico dentro del sistema.
por la naturaleza del software los items van cambiando de estatus en todo el trayecto del dia.
esos estatus junto con el numero de item la fecha y hora son almacenados en la tabla tb_estatus que es donde se llava el control de los mismo.

tb_estatus

Código SQL:

+-------------+----------+------------+---------+-------+
| Last_Status | Hora * * | Fecha * * *| Item1 * | c_id *|
+-------------+----------+------------+---------+-------+
| * * * * * 3 | 10:10:00 | 2012-09-01 | 1201001 |     1 |
+-------------+----------+------------+---------+-------+
 

anteriormente para obtener los estatus de los items por ejemplo del grupo 1 en un periodo de tiempo
tenia que hacer una consulta utilizando JOIN para unir las tablas tb_grupos, tb_items, tb_estatus .
eso lo hacia cuando solo tenia 500.000 registros me funcionaba pero ahora que tengo un escenario de 16M la antigua manera no me sirve,
puesto que he obtado por modificar un poco el diseño y agregarle el campo de grupo al que pertenece el item en la tabla tb_estatus, en este caso el campo c_id

de esta forma me evito tener que hacer un join con la tabla tb_grupos y tb_items y tambien trato de que mysql no tenga que hacer un full scan completo de la tabla para obtener 500.000 registros.

Actualmente solo tengo 4 grupos creados y 16M de estatus generado por parte de los items en general, la data esta desplegada de la siguiente forma.

Código SQL:

+-------+------------+
| c_id *| totalRow * |
+-------+------------+
| * * 1 | 8000000 * *|
| * * 2 | 7000000 * *|
| * * 3 | 500000 * * |
| * * 4 | 500000 * * |
+---------+-------+--+
 

Como pueden notar el c_id = 4 tiene un total de 500.000 reportes que han notificado sus conjuntos de items.

yo suponia que si le decia a mysql que me devuelva los estatus pertenecientes al c_id = 4 mysql solo haria un scan por los 500.000 registros pero me he
dando cuenta que esto no es siempre asi, aplique un EXPLAIN Y obtuve el siguiente resultado

Código SQL:

mysql> EXPLAIN SELECT item1 FROM myTabla WHERE c_id = 4;
+----+-------------+----------+------+---------------+-------+---------+-------+---------+-------+
| id | select_type | TABLE    | TYPE | possible_keys | KEY   | key_len | REF   | ROWS    | Extra |
+----+-------------+----------+------+---------------+-------+---------+-------+---------+-------+
|  1 | SIMPLE      | myTabla  | REF  | INDEX         | INDEX | 2       | const | 1061072 |       |
+----+-------------+----------+------+---------------+-------+---------+-------+---------+-------+
 

Si se fijan mysql scaneo 1061072 para llegar a los 500.000 lo que realmente significa mas del doble.


La tabla de tb_estatus no tenia PK lo que hice fue crearle un PK en un campo autoincrementable y cree un orden para que del 1 al 500.000 solo le pertenezcan
al c_id = 4, nuevamente hice el test y sigo scaniando 1061072.

Por si le sirve trabajo con mysql 5.5 y en un servidor slave o sea que supongo que los indices no se afectan mucho cuando se hacen INSERT en el master.

Saludos!