18/04/2014, 22:39
|
 | | | Fecha de Ingreso: abril-2003 Ubicación: La Paz - Bolivia
Mensajes: 2.003
Antigüedad: 21 años Puntos: 35 | |
 Como optimizar una consulta a una tabla de 5.8 millones de records?
loading.........
Buenas amigos.
Tengo la siguiente tabla con alrededor 5.8 millones de records y va subiendo
Código:
Table: email_addon_rbp_soft_bounces
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| uid | int(11) | NO | PRI | NULL | auto_increment |
| log_id | int(11) | YES | | NULL | |
| from | varchar(255) | YES | | NULL | |
| rcpt | varchar(255) | YES | | NULL | |
| status | varchar(255) | YES | | NULL | |
| message | varchar(255) | YES | | NULL | |
| ip | varchar(45) | YES | | NULL | |
| cat_pmta | varchar(45) | YES | | NULL | |
| date | int(11) | NO | | 0 | |
+----------+--------------+------+-----+---------+----------------+
Y al hacer una consulta tonta por ejemplo:
Código MySQL:
Ver original * email_addon_rbp_soft_bounces log_id != 0
Se demore 8.9 segundos, lo cual creo que no es aceptable.
INDEX:
Código:
mysql> SHOW INDEXES FROM email_addon_rbp_soft_bounces;
+------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| email_addon_rbp_soft_bounces | 0 | PRIMARY | 1 | uid | A | 4563144 | NULL | NULL | | BTREE | | |
+------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
Resultados
Código:
mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 8.09367700 | SELECT * FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rdns%' OR status LIKE '%rdns%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 2 | 8.12044200 | SELECT * FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rdns%' OR status LIKE '%rdns%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 3 | 8.77664800 | SELECT * FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rdns%' OR status LIKE '%rdns%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 4 | 6.74022600 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rdns%' OR status LIKE '%rdns%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 5 | 6.90966600 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rdns%' OR status LIKE '%rdns%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 6 | 7.88677300 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 7 | 7.71468000 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 8 | 7.42245500 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 9 | 8.02358500 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 10 | 6.79185700 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 11 | 6.85658500 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
| 12 | 7.00494600 | SELECT status,message FROM email_addon_rbp_soft_bounces WHERE log_id !=0 AND (message LIKE '%rbl%' OR status LIKE '%rbl%') ORDER BY RAND(),uid DESC LIMIT 10 |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
He intentado, poniendole index a las columnas: status , messages INDEX CONJUNTA:
Código:
mysql> SHOW INDEXES FROM email_addon_rbp_soft_bounces;
+------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| email_addon_rbp_soft_bounces | 0 | PRIMARY | 1 | uid | A | 4703981 | NULL | NULL | | BTREE | | |
| email_addon_rbp_soft_bounces | 1 | search | 1 | status | A | 18 | NULL | NULL | YES | BTREE | | |
| email_addon_rbp_soft_bounces | 1 | search | 2 | message | A | 4703981 | NULL | NULL | YES | BTREE | | |
+------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
connection closed. |