Dudas con un query

Hola, al hacer un join, explain indica que esta usando el indice de la tabla que tiene el join, cuando ambas tablas tienen indices. Esto hace que el query tarde demasiado en traer lo resultados.

Intenté ya usar un force index sin exito.

Cualquier ayuda u orientación se agradece.

PS:el query y las tablas

select *
from contrato sc
left join booking bok on (`bok`.`num_contrato` = `sc`.`num_contrato`)

CREATE TABLE `booking` (
`ndx_booking` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_planta` varchar(5) NOT NULL,
`num_booking` varchar(25) NOT NULL,
`num_contrato` varchar(20) NOT NULL,
`f_book` date NOT NULL,
`buque` varchar(30) NOT NULL,
`num_viaje` varchar(15) DEFAULT NULL,
`naviera` int(10) unsigned NOT NULL,
`ETA` date DEFAULT NULL,
`lugar_salida` varchar(45) NOT NULL,
`lugar_llegada` varchar(45) NOT NULL,
`comentarios` text,
`ETD` date NOT NULL,
PRIMARY KEY (`ndx_booking`),
KEY `book_book` (`num_booking`),
KEY `book_naviera` (`naviera`),
KEY `num_contrato` (`num_contrato`)
)

CREATE TABLE `contrato` (
`id_contrato` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cat_clientes_` int(10) unsigned NOT NULL,
`incoterm` varchar(5) NOT NULL,
`num_contrato` varchar(20) NOT NULL,
`precio` varchar(255) DEFAULT NULL,
`destino` varchar(45) NOT NULL,
`f_alta` date NOT NULL,
`f_embarque` varchar(7) DEFAULT NULL,
`term_pago` int(10) unsigned NOT NULL,
`notas` text,
`estado` varchar(3) NOT NULL,
`id_usuario` int(10) unsigned NOT NULL,
`tipo` int(1) unsigned NOT NULL,
`idioma` int(1) unsigned NOT NULL,
`num_cli_cont` varchar(30) DEFAULT ‘ ‘,
`hora_cont` time NOT NULL COMMENT ‘hora de creacion del cotrato’,
`delivery` varchar(80) DEFAULT NULL COMMENT ‘liberacion de documentos’,
PRIMARY KEY (`id_contrato`),
UNIQUE KEY `sc_sc` (`num_contrato`(10)) USING BTREE,
KEY `contrato_FKIndex1` (`incoterm`),
KEY `contrato_FKIndex2` (`cat_clientes_`),
KEY `sc_idUsuario` (`id_usuario`),
KEY `sc_estado` (`estado`),
KEY `sc_f_alta` (`f_alta`),
KEY `sc_tipo` (`tipo`) USING BTREE
)


0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this post.
Loading...
Bases de Datos, MySQL, Pregúntale al gurú | RSS 2.0 |     645 views

RSS feed

6 Comments »

Comment by El Pop
2009-08-19 17:30:59

umm y no era mas facil hacer un:


Select * from contrato, sc where contrato.num_contrato = sc.num_contrato;


Dale un explain y si el campo de “num_contrato” estan indexado en las dos tablas, debe funcionar en relaciones 1-1 o 1-n.

Comment by chaoslord
2009-08-20 11:31:33

Pop, en respuesta a tu post
el explain:

mysql> explain select * from contrato sc left join booking bok using (num_contrato);
+----+-------------+-------+------+---------------+--------------+---------+----------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+----------------------------+------+-------+
| 1 | SIMPLE | sc | ALL | NULL | NULL | NULL | NULL | 1915 | |
| 1 | SIMPLE | bok | ref | num_contrato | num_contrato | 4 | dev_dimexa.sc.num_contrato | 1 | |
+----+-------------+-------+------+---------------+--------------+---------+----------------------------+------+-------+
2 rows in set (0.01 sec)

los indies de cada tabla:

mysql> show index from contrato;
+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |Index_type | Comment |
+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| contrato | 0 | PRIMARY | 1 | id_contrato | A | 1915 | NULL | NULL | |BTREE | |
| contrato | 0 | sc_sc | 1 | num_contrato | A | 159 | NULL | NULL | |BTREE | |
| contrato | 1 | contrato_FKIndex1 | 1 | incoterm | A | 20 | NULL | NULL | |BTREE | |
| contrato | 1 | contrato_FKIndex2 | 1 | cat_clientes_ | A | 191 | NULL | NULL | |BTREE | |
| contrato | 1 | sc_idUsuario | 1 | id_usuario | A | 14 | NULL | NULL | |BTREE | |
| contrato | 1 | sc_estado | 1 | estado | A | 10 | NULL | NULL | |BTREE | |
| contrato | 1 | sc_f_alta | 1 | f_alta | A | 478 | NULL | NULL | |BTREE | |
| contrato | 1 | sc_tipo | 1 | tipo | A | 1 | NULL | NULL | |BTREE | |
+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.05 sec)

mysql> show index from booking;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| booking | 0 | PRIMARY | 1 | ndx_booking | A | 2381 | NULL | NULL | | BTREE | |
| booking | 1 | book_book | 1 | num_booking | A | 2381 | NULL | NULL | | BTREE | |
| booking | 1 | book_naviera | 1 | naviera | A | 36 | NULL | NULL | | BTREE | |
| booking | 1 | num_contrato | 1 | num_contrato | A | 2381 | NULL | NULL | | BTREE | |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.03 sec)

 
 
Comment by gwolf
2009-08-20 08:22:42

Respondiendo a lo que comenta Pop: En general, es mejor estilo (es más claro para los lectores humanos, al menos — Y no me sorprendería que signifique distintas cosas para el parser, ya que permite optimizar en diferentes momentos) especificar por separado las restricciones de unión de tablas (restringiendo de un producto cartesiano a un resultado referenciado) — Es mejor unir las tablas con un JOIN que en el WHERE. Y como mero azúcar sintáctico (creo), puedes hacer incluso un poco más corta la relación en este caso, cuando las columnas llevan el mismo nombre:

select * from contrato sc left join booking bok using (num_contrato)

Respecto a por qué el motor está omitiendo incluir num_contrato: Por un lado, si una tabla es muy chica, no tiene sentido usar los índices (ya que cabe completa en memoria). Pero dudo que sea el caso. Lo que me llama la atención es que tengas a num_contrato definido como un varchar — Un varchar es muy malo como llave, porque es muy caro hacer las comparaciones, y porque los índices son de uso bastante complicado (hay muchos tipos de índice, adecuados para los diferentes tipos de datos y para diferentes usos en cada uno de ellos). Hay varios campos (p.ej. id_planta, num_viaje) que tienes como varchar y me parece que en realidad deberían ser numéricos porque refieren a atributos en otras tablas.

Además, partiendo del mismo nombre del campo, estamos hablando de un número. ¿Por qué no lo haces entero o numérico? Por úlitmo, si bien estás declarándolos como campos indexados (con KEY), no tienen requisitos de unicidad en ninguna de las dos tablas. ¿Es correcto esto?

De hecho, creo -si entiendo correctamente el planteamiento- que acá estás cayendo en una falta de normalización: Estás repitiendo un atributo que en realidad no es llave. Puede serlo para las búsquedas. Puede que el número de contrato (B-ABC-001-2009) sea una cadena que identifica a cada contrato en el dominio de los humanos, de acuerdo. Pero es un dato completamente irrelevante para cada uno de sus registros (cada entrada de booking). Booking debería referirse a la llave primaria (id_contrato) para ayudarle al motor — Ya cuando se lo reportes al humano puedes empujarle el número.

Y ya entrados en gastos: Veo otros campos sobre los cuales querrás indexar para hacer consultas cualitativas (p.ej. estado, num_cli_cont) que probablemente prefieras que sean numéricos y se refieran a otras tablas. ¡Ah! Y tienes un campo que implica a otro: buque (que podría ser un buque_id como referencia a la tabla buque) implica naviera (buque_id pertenece a buque, que tiene naviera_id y pertenece a naviera). Si no haces muchas consultas referenciando directamente de booking a naviera, puedes quitarle un poco de peso haciendo esta normalización y obteniendo un poco de más homogeneidad, menos redundancia. Obviamente, si haces mucho estas consultas y estás atravesando tres tablas, no te conviene este último pedacito.

Comment by chaoslord
2009-08-20 11:15:27

Si no fuera algo cínico me daría mas pena mostrar parte de los querys.

El Join es parte de un query con varios joins , lo que ocurre es que esta tardando y al checar por que no usa el indice me tope con eso.

En el caso de contrato no pueden repetirse num_contrato, en el caso de la tabla booking si.

El query en cuestion es este:
select `sc`.`num_contrato` AS `num_contrato`,
`rem`.`cancelada` AS `cancelada`,
`rem`.`packing_list` AS `packing_list`
from `contrato` `sc` force index(sc_sc)
force index(sc_tipo)
force index(sc_estado)
join `packing_list` `pl`
join `cant_contenedores` `cc`
join `remisiones` `rem`
left join `booking` `bok` on (
((`bok`.`num_contrato` = `sc`.`num_contrato`)
and (`pl`.`num_contrato` = `sc`.`num_contrato`)
and (`cc`.`num_contrato` = `sc`.`num_contrato`))
)
where (/*(`rem`.`cancelada` = 1)
and*/ (`pl`.`num_contrato` = `sc`.`num_contrato`)
and (`cc`.`num_contrato` = `sc`.`num_contrato`)
and (`rem`.`packing_list` = `pl`.`ndx_packing_list`)
and isnull(`bok`.`ndx_booking`)
" . $planta . "
and (`sc`.`estado` = 0)
and (`sc`.`tipo` 0))
group by `rem`.`packing_list`
having count(`rem`.`packing_list`) > 1

Comment by phylevn
2009-08-20 22:13:14

El comentario de Gunnar es bastante bueno deberías considerar muchos aspectos que ahí comenta. Con respecto a lo que comentas de que num_contrato no se repite en la tabla contratos, pero si en booking, creo que ahí entonces aunque se repita num_contrato en booking deberías hacer referencia a la llave de la tabla contrato que es id_contrato y esa sería tú relación de 1:N para obtener num_contrato y otros datos del contrato., ahora por lo que veo haces muchos join sin sus respectivos on despues del join y supongo que al final en el where es donde los englobas, eso no te lo recomiendo, recuerda que cada vez que haces una relación múltiplicas el número de registros de la tabla A por la Tabla B a unir, y para poder filtrar tienes que poner ahí su respectivo on y no al final. Si quieres evitarte problemas a futuro con el performance de tu aplicación te recomiendo replantear el código sql de tu query. Saludos 🙂

Comment by chaoslord
2009-08-21 11:23:05

El problema es que ya tiene datos (y muchos), pero estoy en eso.

Por otro lado, desde que descubrí el explain extended lo uso en todo query que trabajo.

 
 
 
 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> in your comment.

Trackback responses to this post