18/04/2014, 22:40
|
 | | | Fecha de Ingreso: abril-2003 Ubicación: La Paz - Bolivia
Mensajes: 2.003
Antigüedad: 21 años, 7 meses Puntos: 35 | |
| Respuesta: Como optimizar una consulta a una tabla de 5.8 millones de records?
loading..... CONTINUACION
Resultados:
Código:
mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 7.06934900 | 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 |
| 2 | 7.63701500 | 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 |
| 3 | 8.71020300 | 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 |
| 4 | 8.29961200 | 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 |
| 5 | 7.88496000 | 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 |
| 6 | 8.28817900 | 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 | 6.82828100 | 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.04843600 | 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 | 7.33149200 | 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 | 7.80868100 | 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 | 7.70619800 | 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.96519400 | 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)
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 | 4887070 | NULL | NULL | | BTREE | | |
| email_addon_rbp_soft_bounces | 1 | idx_status | 1 | status | A | 18 | NULL | NULL | YES | BTREE | | |
| email_addon_rbp_soft_bounces | 1 | idx_message | 1 | message | A | 1221767 | NULL | NULL | YES | BTREE | | |
+------------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
Resultados:
Código:
mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 7.21295600 | 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 |
| 2 | 8.13896700 | 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 |
| 3 | 7.25231200 | 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 |
| 4 | 7.38431300 | 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 |
| 5 | 7.39241400 | 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 |
| 6 | 7.51839800 | 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.17190500 | 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.48821300 | 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.23709900 | 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 | 8.32116700 | 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 | 8.09041200 | 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 | 8.41906900 | 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)
No veo muchos cambios.... que podria hacer?
Saludos
Connection closed. |