Como optimizar una consulta a una tabla de 5.8 millones de records?

[MaBoRaK]

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:

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
 

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.

[MaBoRaK]

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)

INDEX SEPARADA

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.

[jurena]

MaBoRaK, sin entrar a valorar los datos que aportas, sino centrándome exclusivamente en la consulta que has probado y llamas 'tonta', te haré algunas preguntas y recomendaciones:
1) no uses el asterisco, salvo que quieras traerte los valores de todos los campos, y tráete solo los campos que necesites en la consulta.
2) log_id no sé qué valores tiene, es decir, qué cardinalidad tiene, para los que somos de letras cuántos distintos valores puede haber. Si son muchos, cambiaría el orden y lo pondría tras (message LIKE '%rdns%'
OR status LIKE '%rdns%') AND log_id != 0. Por otra parte, quizás te compensaría tenerlo indexado.
3) no sé por qué status es una cadena. Parecería más lógico un FKstatus (número indexado) con una tabla de status con primary key PKstatus y relacionada, pero... ya nos explicarás qué tipos de valores guardas ahí para hacer una búsqueda de ese tipo.
4) tampoco tiene mucho sentido para mí una búsqueda en esos dos campos con OR. Explícanos qué sentido tendría buscar la misma parte de cadena en ese campo message o en ese campo status.
5) Y desde luego no entiendo un ORDER BY RAND con el añadido de un uid DESC. Si ordenas ORDER BY RAND, el otro orden no te sirve, porque no podría haber, creo, coincidencia para aplicar el segundo orden. Imagino que lo que quieres es sacar el orden final descendente por uid de los 10 últimos. Yo lo haría con subconsulta luego, una vez tenga los 10 que quiero.
Dinos si vamos muy descaminados y acláranos un poco los detalles y una búsqueda realmente funcional para poner a prueba la base creada. Por otra parte, creo que no es buena idea usar ORDER BY RAND sobre una tabla con 5 millones de registros. Una posible solución sería usar PHP + MySQL para eso. Mira aquí:
http://www.tallerwebmaster.com/tutor...p-y-mysql/132/
Por otra parte, y aunque no afecta a la consulta que propones, no sé por qué no guardas la IP de otra manera, en lugar de hacerlo en un varchar 45. Si tienes que comparar, echa un vistazo aquí para considerar otras opciones de tipo de campo y de función de inserción y de selección:
http://stackoverflow.com/questions/4...sses-for-mysql