"ISC Edwin Cruz" <[EMAIL PROTECTED]> wrote on 11/01/2005 04:33:39 PM:
> 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 *************************** <snipped for space> > 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`), <more snipping> > > mysql> show create table trafico_viaje\G; > *************************** 1. row *************************** <snipped for space> > 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 > > 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] | > +----------------------------------------+ > First, THANK YOU VERY MUCH!!! You actually posted the results of SHOW CREATE TABLE and EXPLAIN. I wish everyone who had a query problem would do those two very simple things. Second, let's look again at what your EXPLAIN is telling you. If you locate the columns "possible_keys" and "key". both are NULL for both tables in your query. That means that there are no indexes that could possibly cover at least one term in your WHERE clause and that none of them were actually used. The database engine has to perform a full-table scan on both tables to solve the query. The column `no_viaje` is used in a few keys but in noe of them is it the left-most term so those indexes cannot be used to find tables using just that column. You already have several indexes defined for both tables so I am concerned about slowing down your INSERT speed with this suggestion however to get that query to respond, you need to add another index to one or both tables ALTER TABLE `trafico_guia` ADD KEY (`no_viaje`, `fecha_guia`); ALTER TABLE `trafico_viaje` ADD KEY(`no_viaje`); Either one will speed up your query but adding both will make it fly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
