0

I have some data on same legacy tables that we've inherited. From those tables I need to make a query from 3 them, but the seems like the join for one of them is taking for ever!

The query itself in basic mode, i.e. retrieving one single record is the following:

select 
    cast(lat as decimal(10,4)) as latitud, 
    cast(lon as decimal(10,4)) as longitud, 
    ot, 
    fecha_despacho as fechaDespacho, 
    cliente_rut as rut, 
    cliente_nombre as cliente, 
    cliente_comuna as comuna, 
    cliente_ciudad as ciudad, 
    id_vehiculo as camion, 
    orden.id_despacho as ruta, 
    orden.fecha_entrega as fechaRuta ,
    hora_entrega as horaEntrega, 
    hora_entrega_final as horaEntregaFinal 
from ot_orden orden 
  inner join ot_despacho despacho 
    on orden.id_despacho=despacho.id_despacho 
  left join sis_localizacion loc 
    on (orden.id_vehiculo=loc.camion 
    and DATE(loc.data) = despacho.fecha_despacho 
    and HOUR(loc.data) = HOUR(orden.hora_entrega_final) 
    and MINUTE(data) BETWEEN MINUTE(orden.hora_entrega_final) -1 
                         AND MINUTE(orden.hora_entrega_final) + 1) 
where orden.fecha_entrega > current_date - interval '30' day 
  and ot=182260

But the problem arises when I delete the final WHERE clause (ot=182260), as it takes for ever to retrieve the records. I guess there may be indexes missing or something like that, but don't really know how dig deeper on this matter by myself.

Explain plan for the query gives the output in the attached follwing image:

Explain plan output for multiple records

Explain plan for the single record output query with ot=182260 gives:

EXplain plan for single record

Should I rephrase the query? add indexes? other?

Table structures:

CREATE TABLE `ot_orden` (
  `ot` bigint(11) NOT NULL,
  `id_estado` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'PN',
  `id_subestado` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `id_estado_tiempo` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `oc` varchar(15) DEFAULT NULL,
  `fecha_entrega` date DEFAULT NULL,
  `cliente_rut` int(11) DEFAULT NULL,
  `cliente_nombre` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `cliente_direccion` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `cliente_comuna` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `cliente_ciudad` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `cliente_telefonos` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `cliente_latlon` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `cliente_email` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `id_despacho` int(11) NOT NULL DEFAULT '0',
  `id_vehiculo` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `hora_entrega` time NOT NULL DEFAULT '00:00:00',
  `hora_entrega_final` time NOT NULL DEFAULT '00:00:00',
  `hora_entrega_limite` time NOT NULL DEFAULT '00:00:00',
  `hora_salida` time NOT NULL DEFAULT '00:00:00',
  `hora_entrega_exedida` time NOT NULL DEFAULT '00:00:00',
  `receptor_rut` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `receptor_nombre` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `receptor_comment` text CHARACTER SET utf8 COLLATE utf8_bin,
  `latitud` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `longitud` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `vendedor_nombre` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `vendedor_email` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `conductor_nombre` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `conductor_telefono` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `hora_carga` time DEFAULT NULL,
  `fecha_carga` date DEFAULT NULL,
  `glosa` text CHARACTER SET utf8 COLLATE utf8_bin,
  PRIMARY KEY (`ot`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ot_despacho` (
  `id_despacho` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_estado` varchar(2) COLLATE latin1_general_ci DEFAULT NULL,
  `usuario` varchar(15) COLLATE latin1_general_ci DEFAULT NULL,
  `fecha_entrega` date DEFAULT NULL,
  `fecha_creacion` date DEFAULT NULL,
  `fecha_envio` date DEFAULT NULL,
  `hora_envio` time DEFAULT NULL,
  `fecha_despacho` date DEFAULT NULL,
  `hora_despacho` time DEFAULT NULL,
  PRIMARY KEY (`id_despacho`)
) ENGINE=MyISAM AUTO_INCREMENT=13583 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci


CREATE TABLE `sis_localizacion` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `lat` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `lon` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `imei` char(50) COLLATE utf8_bin DEFAULT NULL,
  `camion` varchar(4) COLLATE utf8_bin DEFAULT NULL,
  `data` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `camion` (`camion`,`data`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2211134 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Update: Query changed by suggestion to:

select 
    cast(lat as decimal(10,4)) as latitud, 
    cast(lon as decimal(10,4)) as longitud, 
    ot, 
    fecha_despacho as fechaDespacho, 
    cliente_rut as rut, 
    cliente_nombre as cliente, 
    cliente_comuna as comuna, 
    cliente_ciudad as ciudad, 
    id_vehiculo as camion, 
    orden.id_despacho as ruta, 
    orden.fecha_entrega as fechaRuta ,
    hora_entrega as horaEntrega, 
    hora_entrega_final as horaEntregaFinal 
from ot_orden orden 
  inner join ot_despacho despacho 
    on orden.id_despacho=despacho.id_despacho 
  left join sis_localizacion loc 
    on (orden.id_vehiculo=loc.camion 
    and loc.data between orden.fecha_entrega - interval 1 minute and orden.fecha_entrega + interval 1 minute ) 
where orden.fecha_entrega > current_date - interval '30' day 

But it still takes for ever, updated explain gives the following (in CSV format, as don't have enough reputation to post a 3rd image link :( ):

'1', 'SIMPLE', 'orden', 'range', 'idx_fecha_entrega', 'idx_fecha_entrega', '4', NULL, '3775', 'Using where'
'1', 'SIMPLE', 'despacho', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'stl_dimar.orden.id_despacho', '1', 'Using where'
'1', 'SIMPLE', 'loc', 'ref', 'camion,idx_data', 'camion', '15', 'stl_dimar.orden.id_vehiculo', '137552', ''

Further improved performance by lowering rowcount on loc table down to 12380 rows by creating an index on both camion and data columns, but still too many for a fast query response. Actually I get bored waiting for the query to run and never returns.

  • 1
    You need to give us the structure of the tables involved. – jn1kk Nov 19 '15 at 19:08
  • 1
    Can you add explain for the query including `ot=182260``too, for comparison? – jkavalik Nov 19 '15 at 20:40
  • @jsn Table create statements added – Gonzalo Vasquez Nov 20 '15 at 12:14
  • @jkavalik added requested explain plan – Gonzalo Vasquez Nov 20 '15 at 12:16
  • 1
    You have no indexes on the orden table. I suggest at least an index on orden (fecha_entrega). – ypercubeᵀᴹ Nov 20 '15 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.