Re: MYSQL update taking to long need help
Answers under questions
Peter Brawley Wrote:
-------------------------------------------------------
> Those answers don't require a trained DBA. However
> if you're managing these tables, you are
> their DBA.
>
> Read about BBCode tags. Execute these cmds ...
>
> select version();
'5.6.25-log'
>
> Show Create Table schemaa1.cqt;
'cqt', 'CREATE TABLE `cqt` (\n `trayid` int(10) unsigned NOT NULL DEFAULT \'0\',\n `linking` int(10) unsigned NOT NULL,\n `pallet` int(10) unsigned DEFAULT NULL,\n `traytype` varchar(2) DEFAULT NULL,\n `trayact` int(10) unsigned DEFAULT NULL,\n PRIMARY KEY (`trayid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'
>
> Show Create Table schema1.phase3;
'phase3', 'CREATE TABLE `phase3` (\n `num` int(10) unsigned NOT NULL DEFAULT \'0\',\n `stored` decimal(32,0) NOT NULL,\n `readid` varchar(45) DEFAULT NULL,\n `complete` int(1) NOT NULL DEFAULT \'0\',\n `trayid` int(5) unsigned DEFAULT NULL,\n `pallet` int(5) unsigned DEFAULT NULL,\n `linking` int(5) unsigned NOT NULL,\n `backed` int(6) unsigned DEFAULT NULL,\n `traytype` varchar(2) NOT NULL DEFAULT \'0\',\n PRIMARY KEY (`stored`),\n KEY `linking` (`linking`),\n CONSTRAINT `phase3_ibfk_1` FOREIGN KEY (`linking`) REFERENCES `cqt` (`trayid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'
>
> Explain
> update schema1.phase3 as a
> right join schema1.cqt as b on b.trayid =
> a.linking
> set a.trayid = b.trayact, a.pallet = b.pallet ;
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'b', 'ALL', NULL, NULL, NULL, NULL, '18465', NULL
'1', 'SIMPLE', 'a', 'ref', 'linking', 'linking', '4', 'ezmysqltest.b.trayid', '25', NULL
>
> show variables like 'innodb%';
# Variable_name, Value
'innodb_adaptive_flushing', 'ON'
'innodb_adaptive_flushing_lwm', '10'
'innodb_adaptive_hash_index', 'ON'
'innodb_adaptive_max_sleep_delay', '150000'
'innodb_additional_mem_pool_size', '2097152'
'innodb_api_bk_commit_interval', '5'
'innodb_api_disable_rowlock', 'OFF'
'innodb_api_enable_binlog', 'OFF'
'innodb_api_enable_mdl', 'OFF'
'innodb_api_trx_level', '0'
'innodb_autoextend_increment', '64'
'innodb_autoinc_lock_mode', '1'
'innodb_buffer_pool_dump_at_shutdown', 'OFF'
'innodb_buffer_pool_dump_now', 'OFF'
'innodb_buffer_pool_filename', 'ib_buffer_pool'
'innodb_buffer_pool_instances', '8'
'innodb_buffer_pool_load_abort', 'OFF'
'innodb_buffer_pool_load_at_startup', 'OFF'
'innodb_buffer_pool_load_now', 'OFF'
'innodb_buffer_pool_size', '8388608'
'innodb_change_buffer_max_size', '25'
'innodb_change_buffering', 'all'
'innodb_checksum_algorithm', 'crc32'
'innodb_checksums', 'ON'
'innodb_cmp_per_index_enabled', 'OFF'
'innodb_commit_concurrency', '0'
'innodb_compression_failure_threshold_pct', '5'
'innodb_compression_level', '6'
'innodb_compression_pad_pct_max', '50'
'innodb_concurrency_tickets', '5000'
'innodb_data_file_path', 'ibdata1:12M:autoextend'
'innodb_data_home_dir', ''
'innodb_disable_sort_file_cache', 'OFF'
'innodb_doublewrite', 'ON'
'innodb_fast_shutdown', '1'
'innodb_file_format', 'Antelope'
'innodb_file_format_check', 'ON'
'innodb_file_format_max', 'Antelope'
'innodb_file_per_table', 'ON'
'innodb_flush_log_at_timeout', '1'
'innodb_flush_log_at_trx_commit', '1'
'innodb_flush_method', ''
'innodb_flush_neighbors', '1'
'innodb_flushing_avg_loops', '30'
'innodb_force_load_corrupted', 'OFF'
'innodb_force_recovery', '0'
'innodb_ft_aux_table', ''
'innodb_ft_cache_size', '8000000'
'innodb_ft_enable_diag_print', 'OFF'
'innodb_ft_enable_stopword', 'ON'
'innodb_ft_max_token_size', '84'
'innodb_ft_min_token_size', '3'
'innodb_ft_num_word_optimize', '2000'
'innodb_ft_result_cache_limit', '2000000000'
'innodb_ft_server_stopword_table', ''
'innodb_ft_sort_pll_degree', '2'
'innodb_ft_total_cache_size', '640000000'
'innodb_ft_user_stopword_table', ''
'innodb_io_capacity', '200'
'innodb_io_capacity_max', '2000'
'innodb_large_prefix', 'OFF'
'innodb_lock_wait_timeout', '50'
'innodb_locks_unsafe_for_binlog', 'OFF'
'innodb_log_buffer_size', '1048576'
'innodb_log_compressed_pages', 'ON'
'innodb_log_file_size', '50331648'
'innodb_log_files_in_group', '2'
'innodb_log_group_home_dir', '.\\'
'innodb_lru_scan_depth', '1024'
'innodb_max_dirty_pages_pct', '75'
'innodb_max_dirty_pages_pct_lwm', '0'
'innodb_max_purge_lag', '0'
'innodb_max_purge_lag_delay', '0'
'innodb_mirrored_log_groups', '1'
'innodb_monitor_disable', ''
'innodb_monitor_enable', ''
'innodb_monitor_reset', ''
'innodb_monitor_reset_all', ''
'innodb_old_blocks_pct', '37'
'innodb_old_blocks_time', '1000'
'innodb_online_alter_log_max_size', '134217728'
'innodb_open_files', '300'
'innodb_optimize_fulltext_only', 'OFF'
'innodb_page_size', '16384'
'innodb_print_all_deadlocks', 'OFF'
'innodb_purge_batch_size', '300'
'innodb_purge_threads', '1'
'innodb_random_read_ahead', 'OFF'
'innodb_read_ahead_threshold', '56'
'innodb_read_io_threads', '4'
'innodb_read_only', 'OFF'
'innodb_replication_delay', '0'
'innodb_rollback_on_timeout', 'OFF'
'innodb_rollback_segments', '128'
'innodb_sort_buffer_size', '1048576'
'innodb_spin_wait_delay', '6'
'innodb_stats_auto_recalc', 'ON'
'innodb_stats_method', 'nulls_equal'
'innodb_stats_on_metadata', 'OFF'
'innodb_stats_persistent', 'ON'
'innodb_stats_persistent_sample_pages', '20'
'innodb_stats_sample_pages', '8'
'innodb_stats_transient_sample_pages', '8'
'innodb_status_output', 'OFF'
'innodb_status_output_locks', 'OFF'
'innodb_strict_mode', 'OFF'
'innodb_support_xa', 'ON'
'innodb_sync_array_size', '1'
'innodb_sync_spin_loops', '30'
'innodb_table_locks', 'ON'
'innodb_thread_concurrency', '8'
'innodb_thread_sleep_delay', '1250'
'innodb_undo_directory', '.'
'innodb_undo_logs', '128'
'innodb_undo_tablespaces', '0'
'innodb_use_native_aio', 'ON'
'innodb_use_sys_malloc', 'ON'
'innodb_version', '5.6.25'
'innodb_write_io_threads', '4'