Hi All!!
I recently have migrated a database from SQL Server 7.0 to Mysql 5.0.14
(I´ve used MySQL Migration tool), mi probem is that the queries where i use
foreing keys are tooo sloowww
Specialy in two tables;
mysql> show create table trafico_guia\G;
*************************** 1. row ***************************
Table: trafico_guia
Create Table: CREATE TABLE `trafico_guia` (
`id_area` int(10) NOT NULL,
`no_guia` int(10) NOT NULL,
`tipo_doc` smallint(5) NOT NULL,
`num_guia` varchar(20) NOT NULL,
`no_carta` varchar(15) default NULL,
`id_cliente` int(10) NOT NULL,
`id_personal` int(10) NOT NULL,
`no_viaje` int(10) NOT NULL,
`id_remolque1` varchar(10) default NULL,
`id_unidad` varchar(10) NOT NULL,
`fecha_guia` datetime NOT NULL,
`id_remitente` int(10) NOT NULL,
`id_destinatario` int(10) NOT NULL,
`id_origen` smallint(5) NOT NULL,
`id_destino` smallint(5) NOT NULL,
`id_condujo` int(10) default NULL,
`recoger_en` varchar(50) default NULL,
`entregar_en` varchar(50) default NULL,
`valor_unitario` double(53,30) default NULL,
`convenido_tonelada` double(53,30) default NULL,
`id_fraccion` smallint(5) default NULL,
`valor_declarado` varchar(7) default NULL,
`tipo_pago` char(1) NOT NULL,
`flete` decimal(19,4) NOT NULL,
`seguro` decimal(19,4) NOT NULL,
`maniobras` decimal(19,4) NOT NULL,
`autopistas` decimal(19,4) NOT NULL,
`otros` decimal(19,4) NOT NULL,
`subtotal` decimal(19,4) NOT NULL,
`iva_guia` decimal(19,4) NOT NULL,
`observaciones_guia` varchar(250) default NULL,
`no_remision` varchar(20) default NULL,
`status_guia` char(1) NOT NULL,
`motivo_cancelacion` varchar(250) default NULL,
`fecha_confirmacion` datetime default NULL,
`fecha_vencimiento` datetime default NULL,
`fecha_pago` datetime default NULL,
`obs_cobranza` varchar(250) default NULL,
`plaza_emision` smallint(5) NOT NULL,
`tipo_cobro` char(1) default NULL,
`cobro_viaje_kms` decimal(19,4) default NULL,
`kms_guia` int(10) default NULL,
`conducir_de` smallint(5) NOT NULL,
`conducir_a` smallint(5) NOT NULL,
`id_remolque2` varchar(10) default NULL,
`prestamo` varchar(1) NOT NULL,
`id_tipo_moneda` smallint(5) NOT NULL,
`monto_tipo_cambio` decimal(19,4) NOT NULL,
`id_ingreso` varchar(8) default NULL,
`fecha_ingreso` datetime default NULL,
`num_guia_asignado` varchar(20) default NULL,
`no_deposito` int(10) default NULL,
`id_serieguia` int(10) default NULL,
`unidadplaca` varchar(20) default NULL,
`unidadtipo` varchar(40) default NULL,
`rem1placa` varchar(20) default NULL,
`rem1tipo` varchar(40) default NULL,
`rem2placa` varchar(20) default NULL,
`rem2tipo` varchar(40) default NULL,
`personalnombre` varchar(80) default NULL,
`monto_ncredito` decimal(19,4) default NULL,
`tipo_facturacion` varchar(25) default NULL,
`monto_ncargo` decimal(19,4) default NULL,
`monto_pago` decimal(19,4) default NULL,
`factor_iva` double(53,30) default NULL,
`sustituye_documento` varchar(20) default NULL,
`monto_ivancredito` decimal(19,4) default NULL,
`cant_movguia` tinyint(3) default NULL,
`monto_ivancargo` decimal(19,4) default NULL,
`tipo_origen` tinyint(3) default NULL,
`nota_entrega` varchar(25) default NULL,
`factura_pemex` varchar(25) default NULL,
`vale_pemex` varchar(25) default NULL,
`pedimento` varchar(25) default NULL,
`factura_cliente` varchar(25) default NULL,
`tipo_iva` varchar(1) default NULL,
`fecha_cancelacion` datetime default NULL,
`fecha_contabilizado` datetime default NULL,
`clasificacion_doc` tinyint(3) default NULL,
`no_transferencia_cobranza` int(10) default NULL,
`conv_operador` double(53,30) default NULL,
`conv_permisionario` double(53,30) default NULL,
`num_guiacancel` varchar(25) default NULL,
`monto_comisiontercero` double(53,30) default NULL,
`id_linearem1` varchar(10) default NULL,
`id_linearem2` varchar(10) default NULL,
`monto_retencion` decimal(18,6) default NULL,
PRIMARY KEY (`id_area`,`no_guia`),
UNIQUE KEY `xak_trafnumguia` (`num_guia`),
KEY `XAK1status_guia` (`status_guia`),
KEY `fk_clasificadoctrafguia` (`clasificacion_doc`),
KEY `fk_guia_conducir` (`id_personal`),
KEY `fk_guiadestino` (`id_destino`),
KEY `fk_guiamoneda` (`id_tipo_moneda`),
KEY `fk_guiaunidad` (`id_unidad`),
KEY `fk1_cliente` (`id_cliente`),
KEY `fk1_destinatario` (`id_destinatario`),
KEY `fk1_guiacarta` (`id_area`,`no_carta`),
KEY `fk1_no_viaje` (`id_area`,`no_viaje`),
KEY `fk1_origen` (`id_origen`),
KEY `fk1_remitente` (`id_remitente`),
CONSTRAINT `fk1_cliente` FOREIGN KEY (`id_cliente`) REFERENCES
`trafico_cliente` (`id_cliente`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk1_destinatario` FOREIGN KEY (`id_destinatario`) REFERENCES
`trafico_cliente` (`id_cliente`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk1_guiacarta` FOREIGN KEY (`id_area`, `no_carta`) REFERENCES
`trafico_carta_cobro` (`id_area`, `no_carta`) ON DELETE NO ACTION ON UPDATE
NO ACTION,
CONSTRAINT `fk1_no_viaje` FOREIGN KEY (`id_area`, `no_viaje`) REFERENCES
`trafico_viaje` (`id_area`, `no_viaje`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
CONSTRAINT `fk1_origen` FOREIGN KEY (`id_origen`) REFERENCES
`trafico_plaza` (`id_plaza`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk1_remitente` FOREIGN KEY (`id_remitente`) REFERENCES
`trafico_cliente` (`id_cliente`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_clasificadoctrafguia` FOREIGN KEY (`clasificacion_doc`)
REFERENCES `trafico_clasifica_doc` (`id_clasificacion_doc`) ON DELETE NO
ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_guiadestino` FOREIGN KEY (`id_destino`) REFERENCES
`trafico_plaza` (`id_plaza`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_guiamoneda` FOREIGN KEY (`id_tipo_moneda`) REFERENCES
`compras_tipo_moneda` (`id_tipo_moneda`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
CONSTRAINT `fk_guiaunidad` FOREIGN KEY (`id_unidad`) REFERENCES
`mtto_unidades` (`id_unidad`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_guia_area` FOREIGN KEY (`id_area`) REFERENCES
`general_area` (`id_area`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_guia_conducir` FOREIGN KEY (`id_personal`) REFERENCES
`personal_personal` (`id_personal`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.88 sec)
(note that this query´s been executed in almost a second
mysql> show create table trafico_viaje\G;
*************************** 1. row ***************************
Table: trafico_viaje
Create Table: CREATE TABLE `trafico_viaje` (
`id_area` int(10) NOT NULL,
`no_viaje` int(10) NOT NULL,
`id_unidad` varchar(10) default NULL,
`id_personal` int(10) default NULL,
`fecha_viaje` datetime default NULL,
`kms_camion_vacio` int(10) default NULL,
`kms_camion_lleno` int(10) default NULL,
`kms_operador` int(10) default NULL,
`lts_viaje` double(53,30) default NULL,
`rendimiento_viaje` double(53,30) default NULL,
`rendimiento_real` double(53,30) default NULL,
`status_viaje` char(1) NOT NULL,
`kms_real` int(10) default NULL,
`fecha_fin_viaje` datetime default NULL,
`id_ruta` smallint(5) NOT NULL,
`no_liquidacion` int(10) default NULL,
`sueldo_operador` decimal(19,4) default NULL,
`compensaciones` decimal(19,4) default NULL,
`fecha_real_viaje` datetime default NULL,
`fecha_real_fin_viaje` datetime default NULL,
`lts_empresa` double(53,30) default NULL,
`id_remolque1` varchar(10) default NULL,
`id_remolque2` varchar(10) default NULL,
`rend_esperado` double(53,30) default NULL,
`id_sector` tinyint(3) default NULL,
`id_subsector` tinyint(3) default NULL,
`viaje_ant` int(10) default NULL,
`no_liquidacion_rend` int(10) default NULL,
`id_dolly` varchar(10) default NULL,
`id_ingreso` varchar(8) default NULL,
`fecha_ingreso` datetime default NULL,
`puntaje_viaje` int(10) default NULL,
`no_kit` varchar(10) default NULL,
`thermo1` varchar(10) default NULL,
`thermo2` varchar(10) default NULL,
`lts_empthermo1` double(53,30) default NULL,
`lts_empthermo2` double(53,30) default NULL,
`hrs_realthermo1` int(10) default NULL,
`hrs_realthermo2` int(10) default NULL,
`rent_espthermo1` double(53,30) default NULL,
`rent_espthermo2` double(53,30) default NULL,
`rent_realthermo1` double(53,30) default NULL,
`rent_realthermo2` double(53,30) default NULL,
`lts_viajethermo1` double(53,30) default NULL,
`lts_viajethermo2` double(53,30) default NULL,
`rend_espthermo1` double(53,30) default NULL,
`rend_espthermo2` double(53,30) default NULL,
`rend_realthermo1` double(53,30) default NULL,
`rend_realthermo2` double(53,30) default NULL,
`viajeactual` char(1) default NULL,
`status_desp` char(1) default NULL,
`no_ejes` int(10) default NULL,
`id_linearem1` varchar(10) default NULL,
`id_linearem2` varchar(10) default NULL,
PRIMARY KEY (`id_area`,`no_viaje`),
KEY `fk_viajepersonal` (`id_personal`),
KEY `fk_viajeruta` (`id_ruta`),
CONSTRAINT `fk_viajearea` FOREIGN KEY (`id_area`) REFERENCES
`general_area` (`id_area`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_viajepersonal` FOREIGN KEY (`id_personal`) REFERENCES
`personal_personal` (`id_personal`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_viajeruta` FOREIGN KEY (`id_ruta`) REFERENCES
`trafico_ruta` (`id_ruta`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.17 sec)
When I try this query it never finishes
select a.id_unidad,
b.fecha_guia,
b.monto_pago*b.monto_tipo_cambio
from trafico_viaje a,trafico_guia b
where a.no_viaje=b.no_viaje
and b.fecha_guia between '2005/01/01' and '2005/01/31';
My explain result shows:
mysql> explain select
a.id_unidad,b.fecha_guia,b.monto_pago*b.monto_tipo_cambio from trafico_viaje
a,trafico_guia b where a.no_viaje=b.no_viaje and b.fecha_guia between
'2005/01/01' and '2005/01/31';
+----+-------------+-------+------+---------------+------+---------+------+-
------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-
------+-------------+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL |
37851 | Using where |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL |
54058 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-
------+-------------+
2 rows in set (0.04 sec)
I´ve tryed with FORCE KEY, USING,etc. But those don´t work.
Should I try changing from InnoDB storage engine to MyISAM ??
mysql> show variables like 'Inno%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+------------------------+
33 rows in set (0.00 sec)
Tanks a lot for any suggestion
+----------------------------------------+
| ISC Edwin Cruz García |
| Gerente de T.I. |
| +--------------------+ --\____ |
| | | | | \ |
| | Transportes medel | | | +---+ |
| +-------------------===+--+_______ï |
| (ô)(ô) (ô)(ô) (ô)*******(ô) ¨ |
| |
| Tel. 01 (449) 910 30 90 x3054 |
| Móvil: 01 (449) 111 29 03 |
| email: [EMAIL PROTECTED] |
+----------------------------------------+
