select demora mucho en obrener resultados

[pixie72]

Iniciado por pixie72

Hola foro.

Tengo una consulta que me esta matando. Es un simple select que demora 2 minutos. El log generado por mysql es el siguiente

110127 11:37:27 20 Connect root@pcamd1 on pvweb_u25
20 Query SELECT @@sql_mode
20 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION'
20 Query SET NAMES utf8
20 Init DB pvweb_u25
20 Query SELECT @@SQL_MODE
20 Query SHOW FULL COLUMNS FROM `pvweb_u25`.`productos`
20 Query SHOW CREATE TABLE `pvweb_u25`.`productos`
20 Query select * from productos where (pronom like '%ferrari%')
110127 11:37:30 20 Quit



110127 11:37:41 21 Connect root@pcamd1 on pvweb_u25
21 Query SELECT @@sql_mode
21 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION'
21 Query SET NAMES utf8
21 Init DB pvweb_u25
21 Query SELECT @@SQL_MODE
21 Query SHOW FULL COLUMNS FROM `pvweb_u25`.`productos`
21 Query SHOW CREATE TABLE `pvweb_u25`.`productos`
21 Query select procod, pronom from productos where (pronom like '%ferrari%') order by pronom
110127 11:39:33 21 Quit




Como ven es un simple select con un like que tiene comodines ( % ), en el primer caso lo hace enseguida porque pido todo los campos y sin orden. Pero cuando lo quiero ordenado demora mucho.
La tabla tiene 160.000 registros cosa que no considero demasiado

Alguna idea para optimizar esto y bajar los 2 minutos de demora ???
Gracias desde ya

[gnzsoloyo]

Como ven es un simple select con un like que tiene comodines ( % ), en el primer caso lo hace enseguida porque pido todo los campos y sin orden. Pero cuando lo quiero ordenado demora mucho.
La tabla tiene 160.000 registros cosa que no considero demasiado

Una consulta con LIKE, lamentablemente, no es una consulta "simple". Es el tipo de consultas de baja eficiencia porque lo que busca es similitudes, y las similitudes requieren e principio un escaneo de toda la tabla. Siempre.
En ese sentido es más eficiente, si vas a usar LIKE, usarlo con campos con índices y definir que lo usará (FORCE INDEX ....) al leer la tabla, antes que usar un LIKE pelado.
Además de eso, estás invocando un ORDER BY, que junto con GROUP BY son las clausulas que más consumen tiempo y recursos, por lo que si la consulta se tarda demasiado, bueno, no tiene mucha más solución que modificar la configuración del servidor (sort_buffer, log_slow_queries)l o bien el hardware, para optimizarlo.
Ten en cuenta también, que todo ordenamiento por campo no clave o no perteneciente a un INDEX, insumirá siempre más tiempo, porque debe ordenar por comparación de valores... y a más valores, más tiempo.

[pixie72]

Gnzsoloyo, muchas gracias por tu aporte.
Comparto todo lo que dices, es tal cual, pero si revisas lo que escribi te daràs cuenta que la primer sentencia es con like y demora 3 décimas de segundo en resolverla.
La segunda solo deberia tomar esos registros ( que tardó 3 dècimas ) y ordenarlos.

En este rato he seguido probando. Luego de leer tu respuesta saqué el order by.
Mira el resultado:

110127 12:45:18 10 Connect root@pcamd1 on pvweb_u25
10 Query SELECT @@sql_mode
10 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION'
10 Query SET NAMES utf8
10 Init DB pvweb_u25
10 Query SELECT @@SQL_MODE
10 Query SHOW FULL COLUMNS FROM `pvweb_u25`.`Productos`
10 Query SHOW CREATE TABLE `pvweb_u25`.`Productos`
10 Query SELECT `ProCod`, `ProNom` FROM `Productos` WHERE (`ProNom` like '%ferrari%')
110127 12:46:31 10 Quit

O sea.. Un minuto y medio.
No tiene ninguna razón ( a mi entender al menos ) de que si obtiene los registros en 3 décimas, luego demore minuto y medio para darme 2 campos de ese resultado.

[gnzsoloyo]

¿Por qué en lugar de leer el log, no usas EXPLAIN para que te muestre qué es lo que hace en un caso y en otro?

Código MySQL:

EXPLAIN SELECT procod, pronom FROM productos WHERE pronom like '%ferrari%' ORDER BY pronom;
EXPLAIN SELECT procod, pronom FROM productos WHERE pronom like '%ferrari%';
EXPLAIN SELECT `ProCod`, `ProNom` FROM `Productos` WHERE (`ProNom` like '%ferrari%');
 

[pixie72]

gracias gnzsoloyo, paso aca los explain, no entiendo mucho como interpretarlos pero capaz que me ayudan. Por lo que veo recorren la misma cantidad de registros pero la segunda accede por el indice UPRODUCTOS ( este indice es por el campo pronom )



mysql> explain select * from productos where (pronom like '%visual%');
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | productos | ALL | NULL | NULL | NULL | NULL | 160081 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set





mysql> explain select procod,pronom from productos where (pronom like '%visual%');
+----+-------------+-----------+-------+---------------+------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | productos | index | NULL | UPRODUCTOS | 250 | NULL | 160081 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------------+---------+------+--------+--------------------------+
1 row in set