automatem提出的问题 -dba

automatem

Asked: 2017-10-06 00:21:42 +0800 CST

Muitas conexões do AWS RDS causam "bloqueio"

1

Eu executo uma pilha LAMP bastante padrão com um banco de dados Amazon RDS em uma instância t2.small . Hoje fui alertado através do monitoramento do site que o banco de dados parecia ter "travado" completamente. Os sintomas que encontrei foram:

Nenhuma consulta em execução por muito tempo durante a execução
```
  show processlist
```
Servidor web Apache não está ocupado

Revisando os logs configurados no Cloudwatch mostrados no momento da interrupção, descobri que todas as métricas de taxa de transferência chegaram perto de 0. As conexões chegaram a um pico de 17. O limite de conexão é configurado por padrão para InstanceMemory/12 MB, ou seja, cerca de 160 conexões. A memória liberável passou de 264 MB para 234 MB.

O gráfico de saldo de crédito da CPU para a instância do RDS mostra que a CPU está em 250 consistentemente, o servidor web também está muito alto. Não há tráfego específico que eu possa apontar, selecionei alguns URLs dos logs do servidor da Web e contei a solicitação naquela hora e em comparação com outras horas. Nada que se destaque.

Como eu poderia diagnosticar isso melhor?

`SHOW VARIABLES`

    auto_increment_increment    1
    auto_increment_offset   1
    autocommit  ON
    automatic_sp_privileges ON
    avoid_temporal_upgrade  OFF
    back_log    80
    basedir /rdsdbbin/mysql/
    big_tables  OFF
    bind_address    *
    binlog_cache_size   32768
    binlog_checksum CRC32
    binlog_direct_non_transactional_updates OFF
    binlog_error_action IGNORE_ERROR
    binlog_format   MIXED
    binlog_gtid_simple_recovery OFF
    binlog_max_flush_queue_time 0
    binlog_order_commits    ON
    binlog_row_image    FULL
    binlog_rows_query_log_events    OFF
    binlog_stmt_cache_size  32768
    binlogging_impossible_mode  IGNORE_ERROR
    block_encryption_mode   aes-128-ecb
    bulk_insert_buffer_size 8388608
    character_set_client    utf8
    character_set_connection    utf8
    character_set_database  utf8
    character_set_filesystem    binary
    character_set_results   utf8
    character_set_server    utf8
    character_set_system    utf8
    character_sets_dir  /rdsdbbin/mysql-5.6.27.R1/share/charsets/
    collation_connection    utf8_general_ci
    collation_database  utf8_general_ci
    collation_server    utf8_general_ci
    completion_type NO_CHAIN
    concurrent_insert   AUTO
    connect_timeout 10
    core_file   OFF
    datadir /rdsdbdata/db/
    date_format %Y-%m-%d
    datetime_format %Y-%m-%d %H:%i:%s
    default_storage_engine  InnoDB
    default_tmp_storage_engine  InnoDB
    default_week_format 0
    delay_key_write ON
    delayed_insert_limit    100
    delayed_insert_timeout  300
    delayed_queue_size  1000
    disconnect_on_expired_password  ON
    div_precision_increment 4
    end_markers_in_json OFF
    enforce_gtid_consistency    OFF
    eq_range_index_dive_limit   10
    error_count 0
    event_scheduler OFF
    expire_logs_days    0
    explicit_defaults_for_timestamp ON
    external_user   
    flush   OFF
    flush_time  0
    foreign_key_checks  ON
    ft_boolean_syntax   + -><()~*:""&|
    ft_max_word_len 84
    ft_min_word_len 4
    ft_query_expansion_limit    20
    ft_stopword_file    (built-in)
    general_log OFF
    general_log_file    /rdsdbdata/log/general/mysql-general.log
    group_concat_max_len    1024
    gtid_executed   
    gtid_mode   OFF
    gtid_next   AUTOMATIC
    gtid_owned  
    gtid_purged 
    have_compress   YES
    have_crypt  YES
    have_dynamic_loading    YES
    have_geometry   YES
    have_openssl    YES
    have_profiling  YES
    have_query_cache    YES
    have_rtree_keys YES
    have_ssl    YES
    have_symlink    YES
    host_cache_size 278
    hostname    ip-10-6-0-211
    identity    0
    ignore_builtin_innodb   OFF
    ignore_db_dirs  
    init_connect    
    init_file   
    init_slave  
    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 8388608
    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 1418723328
    innodb_change_buffer_max_size   25
    innodb_change_buffering all
    innodb_checksum_algorithm   innodb
    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    /rdsdbdata/db/innodb
    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 O_DIRECT
    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  8388608
    innodb_log_compressed_pages ON
    innodb_log_file_size    134217728
    innodb_log_files_in_group   2
    innodb_log_group_home_dir   /rdsdbdata/log/innodb
    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   2000
    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   0
    innodb_thread_sleep_delay   10000
    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.27
    innodb_write_io_threads 4
    insert_id   0
    interactive_timeout 28800
    join_buffer_size    262144
    keep_files_on_create    OFF
    key_buffer_size 16777216
    key_cache_age_threshold 300
    key_cache_block_size    1024
    key_cache_division_limit    100
    large_files_support ON
    large_page_size 0
    large_pages OFF
    last_insert_id  0
    lc_messages en_US
    lc_messages_dir /rdsdbbin/mysql-5.6.27.R1/share/
    lc_time_names   en_US
    license GPL
    local_infile    ON
    lock_wait_timeout   31536000
    locked_in_memory    OFF
    log_bin ON
    log_bin_basename    /rdsdbdata/log/binlog/mysql-bin-changelog
    log_bin_index   /rdsdbdata/log/binlog/mysql-bin-changelog.index
    log_bin_trust_function_creators OFF
    log_bin_use_v1_row_events   OFF
    log_error   /rdsdbdata/log/error/mysql-error.log
    log_output  TABLE
    log_queries_not_using_indexes   OFF
    log_slave_updates   ON
    log_slow_admin_statements   OFF
    log_slow_slave_statements   OFF
    log_throttle_queries_not_using_indexes  0
    log_warnings    1
    long_query_time 10.000000
    low_priority_updates    OFF
    lower_case_file_system  OFF
    lower_case_table_names  0
    master_info_repository  TABLE
    master_verify_checksum  OFF
    max_allowed_packet  33554432
    max_binlog_cache_size   18446744073709547520
    max_binlog_size 134217728
    max_binlog_stmt_cache_size  18446744073709547520
    max_connect_errors  100
    max_connections 150
    max_delayed_threads 20
    max_digest_length   1024
    max_error_count 64
    max_heap_table_size 402653184
    max_insert_delayed_threads  20
    max_join_size   18446744073709551615
    max_length_for_sort_data    1024
    max_prepared_stmt_count 16382
    max_relay_log_size  0
    max_seeks_for_key   18446744073709551615
    max_sort_length 1024
    max_sp_recursion_depth  0
    max_tmp_tables  32
    max_user_connections    0
    max_write_lock_count    18446744073709551615
    metadata_locks_cache_size   1024
    metadata_locks_hash_instances   8
    min_examined_row_limit  0
    multi_range_count   256
    myisam_data_pointer_size    6
    myisam_max_sort_file_size   9223372036853727232
    myisam_mmap_size    18446744073709551615
    myisam_recover_options  OFF
    myisam_repair_threads   1
    myisam_sort_buffer_size 8388608
    myisam_stats_method nulls_unequal
    myisam_use_mmap OFF
    net_buffer_length   16384
    net_read_timeout    30
    net_retry_count 10
    net_write_timeout   60
    new OFF
    old OFF
    old_alter_table OFF
    old_passwords   0
    open_files_limit    65535
    optimizer_prune_level   1
    optimizer_search_depth  62
    optimizer_switch    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
    optimizer_trace enabled=off,one_line=off
    optimizer_trace_features    greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
    optimizer_trace_limit   1
    optimizer_trace_max_mem_size    16384
    optimizer_trace_offset  -1
    performance_schema  OFF
    performance_schema_accounts_size    -1
    performance_schema_digests_size -1
    performance_schema_events_stages_history_long_size  -1
    performance_schema_events_stages_history_size   -1
    performance_schema_events_statements_history_long_size  -1
    performance_schema_events_statements_history_size   -1
    performance_schema_events_waits_history_long_size   -1
    performance_schema_events_waits_history_size    -1
    performance_schema_hosts_size   -1
    performance_schema_max_cond_classes 80
    performance_schema_max_cond_instances   -1
    performance_schema_max_digest_length    1024
    performance_schema_max_file_classes 50
    performance_schema_max_file_handles 32768
    performance_schema_max_file_instances   -1
    performance_schema_max_mutex_classes    200
    performance_schema_max_mutex_instances  -1
    performance_schema_max_rwlock_classes   40
    performance_schema_max_rwlock_instances -1
    performance_schema_max_socket_classes   10
    performance_schema_max_socket_instances -1
    performance_schema_max_stage_classes    150
    performance_schema_max_statement_classes    168
    performance_schema_max_table_handles    -1
    performance_schema_max_table_instances  -1
    performance_schema_max_thread_classes   50
    performance_schema_max_thread_instances -1
    performance_schema_session_connect_attrs_size   -1
    performance_schema_setup_actors_size    100
    performance_schema_setup_objects_size   100
    performance_schema_users_size   -1
    pid_file    /rdsdbdata/log/mysql-3306.pid
    plugin_dir  /rdsdbbin/mysql-5.6.27.R1/lib/plugin/
    port    3306
    preload_buffer_size 32768
    profiling   OFF
    profiling_history_size  15
    protocol_version    10
    proxy_user  
    pseudo_slave_mode   OFF
    pseudo_thread_id    5139681
    query_alloc_block_size  8192
    query_cache_limit   1048576
    query_cache_min_res_unit    4096
    query_cache_size    1048576
    query_cache_type    OFF
    query_cache_wlock_invalidate    OFF
    query_prealloc_size 8192
    rand_seed1  0
    rand_seed2  0
    range_alloc_block_size  4096
    read_buffer_size    262144
    read_only   OFF
    read_rnd_buffer_size    524288
    relay_log   /rdsdbdata/log/relaylog/relaylog
    relay_log_basename  /rdsdbdata/log/relaylog/relaylog
    relay_log_index /rdsdbdata/log/relaylog/relaylog.index
    relay_log_info_file relay-log.info
    relay_log_info_repository   TABLE
    relay_log_purge ON
    relay_log_recovery  ON
    relay_log_space_limit   0
    report_host 
    report_password 
    report_port 3306
    report_user 
    rpl_stop_slave_timeout  31536000
    secure_auth ON
    secure_file_priv    /tmp/
    server_id   153211832
    server_id_bits  32
    server_uuid 7558fb76-4974-11e6-b1d8-067680180113
    show_old_temporals  OFF
    simplified_binlog_gtid_recovery OFF
    skip_external_locking   ON
    skip_name_resolve   OFF
    skip_networking OFF
    skip_show_database  OFF
    slave_allow_batching    OFF
    slave_checkpoint_group  512
    slave_checkpoint_period 300
    slave_compressed_protocol   OFF
    slave_exec_mode STRICT
    slave_load_tmpdir   /rdsdbdata/tmp
    slave_max_allowed_packet    1073741824
    slave_net_timeout   3600
    slave_parallel_workers  0
    slave_pending_jobs_size_max 16777216
    slave_rows_search_algorithms    TABLE_SCAN,INDEX_SCAN
    slave_skip_errors   OFF
    slave_sql_verify_checksum   ON
    slave_transaction_retries   10
    slave_type_conversions  
    slow_launch_time    2
    slow_query_log  OFF
    slow_query_log_file /rdsdbdata/log/slowquery/mysql-slowquery.log
    socket  /tmp/mysql.sock
    sort_buffer_size    262144
    sql_auto_is_null    OFF
    sql_big_selects ON
    sql_buffer_result   OFF
    sql_log_bin ON
    sql_log_off OFF
    sql_mode    NO_ENGINE_SUBSTITUTION
    sql_notes   ON
    sql_quote_show_create   ON
    sql_safe_updates    OFF
    sql_select_limit    18446744073709551615
    sql_slave_skip_counter  0
    sql_warnings    OFF
    ssl_ca  /rdsdbdata/rds-metadata/ca-cert.pem
    ssl_capath  
    ssl_cert    /rdsdbdata/rds-metadata/server-cert.pem
    ssl_cipher  EXP1024-RC4-SHA:EXP1024-DES-CBC-SHA:AES256-SHA:AES128-SHA:DES-CBC3-SHA:DES-CBC-SHA:EXP-DES-CBC-SHA:EXP-RC2-CBC-MD5:RC4-SHA:RC4-MD5:EXP-RC4-MD5:NULL-SHA:NULL-MD5:DES-CBC3-MD5:DES-CBC-MD5:EXP-RC2-CBC-MD5:RC2-CBC-MD5:EXP-RC4-MD5:RC4-MD5:KRB5-DES-CBC3-MD5:KRB5-DES-CBC3-SHA:ADH-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:EDH-DSS-DES-CBC3-SHA:ADH-AES256-SHA:DHE-RSA-AES256-SHA:DHE-DSS-AES256-SHA:ADH-AES128-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA:EXP-KRB5-RC4-MD5:EXP-KRB5-RC2-CBC-MD5:EXP-KRB5-DES-CBC-MD5:KRB5-RC4-MD5:KRB5-DES-CBC-MD5:ADH-RC4-MD5:EXP-ADH-RC4-MD5:DHE-DSS-RC4-SHA:EXP1024-DHE-DSS-RC4-SHA:EXP1024-DHE-DSS-DES-CBC-SHA:EXP-KRB5-RC4-SHA:EXP-KRB5-RC2-CBC-SHA:EXP-KRB5-DES-CBC-SHA:KRB5-RC4-SHA:KRB5-DES-CBC-SHA:ADH-DES-CBC-SHA:EXP-ADH-DES-CBC-SHA:EDH-RSA-DES-CBC-SHA:EXP-EDH-RSA-DES-CBC-SHA:EDH-DSS-DES-CBC-SHA:EXP-EDH-DSS-DES-CBC-SHA
    ssl_crl 
    ssl_crlpath 
    ssl_key /rdsdbdata/rds-metadata/server-key.pem
    storage_engine  InnoDB
    stored_program_cache    256
    sync_binlog 1
    sync_frm    ON
    sync_master_info    10000
    sync_relay_log  10000
    sync_relay_log_info 10000
    system_time_zone    UTC
    table_definition_cache  1400
    table_open_cache    2000
    table_open_cache_instances  16
    thread_cache_size   9
    thread_concurrency  10
    thread_handling one-thread-per-connection
    thread_stack    262144
    time_format %H:%i:%s
    time_zone   UTC
    timed_mutexes   OFF
    timestamp   1507496697.301036
    tmp_table_size  16777216
    tmpdir  /rdsdbdata/tmp
    transaction_alloc_block_size    8192
    transaction_allow_batching  OFF
    transaction_prealloc_size   4096
    tx_isolation    REPEATABLE-READ
    tx_read_only    OFF
    unique_checks   ON
    updatable_views_with_limit  YES
    version 5.6.27-log
    version_comment MySQL Community Server (GPL)
    version_compile_machine x86_64
    version_compile_os  Linux
    wait_timeout    28800
    warning_count   0

automatem

Asked: 2015-09-16 18:28:44 +0800 CST

O índice composto funciona para um conjunto maior de dados, mas não menor

2

Esta é uma pergunta seguinte para otimizar my.ini para consulta de chave

Modelo de dados:

DROP TABLE IF EXISTS `contract`;
CREATE TABLE IF NOT EXISTS `contract` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) NOT NULL,
  `code` varchar(3) NOT NULL,
  `moh_code` varchar(20) NOT NULL,
  `moh_number` varchar(20) NOT NULL,
  `moh_variation` varchar(20) NOT NULL,
  `description` mediumtext NOT NULL,
  `start` datetime NOT NULL,
  `finish` datetime NOT NULL,
  `cities` text,
  `is_support_contract` tinyint(1) DEFAULT NULL,
  `is_intensive` tinyint(1) DEFAULT NULL,
  `moh_team_type` varchar(4) DEFAULT NULL,
  `moh_team_setting` varchar(1) DEFAULT NULL,
  `moh_service_type` varchar(2) DEFAULT NULL,
  `moh_target_population` int(4) DEFAULT NULL,
  `moh_facility_id` varchar(10) DEFAULT NULL,
  `moh_open_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `organisation_id_idx` (`organisation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_engagement`
--

DROP TABLE IF EXISTS `peer_engagement`;
CREATE TABLE IF NOT EXISTS `peer_engagement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `peer_id` int(11) NOT NULL,
  `ps_number_id` int(11) NOT NULL,
  `service_organisation_id` int(11) DEFAULT NULL,
  `past_service_disengaged` datetime DEFAULT NULL,
  `key_worker_id` int(11) DEFAULT NULL,
  `iss_clinical_service_id` int(11) DEFAULT NULL,
  `iss_psychiatrist_id` int(11) DEFAULT NULL,
  `iss_service_coordinator_id` int(11) DEFAULT NULL,
  `iss_declined_courier_back_address` varchar(255) DEFAULT NULL,
  `waitinglist_support_suggestions_made` mediumtext,
  `waiting_psw_id` int(11) DEFAULT NULL,
  `declined_waiting_what_support_avail` mediumtext,
  `referral_source_id` int(11) DEFAULT NULL,
  `iss_referred_by` int(11) DEFAULT NULL,
  `record_entered_by` int(11) NOT NULL,
  `record_entered` datetime NOT NULL,
  `waiting_list_priority_id` int(11) DEFAULT NULL,
  `assigned_psw_id` int(11) DEFAULT NULL,
  `assigned` datetime DEFAULT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `assigned_effective` datetime DEFAULT NULL,
  `last_reengaged` datetime DEFAULT NULL,
  `last_reengaged_id` int(11) DEFAULT NULL,
  `disengagement_started` datetime DEFAULT NULL,
  `disengagement_target` datetime DEFAULT NULL,
  `disengagement_type_id` int(11) DEFAULT NULL,
  `disengagement_comments` mediumtext,
  `status_id` int(11) DEFAULT NULL,
  `closed` datetime DEFAULT NULL,
  `closed_by` int(11) DEFAULT NULL,
  `is_intensive` varchar(5) DEFAULT NULL,
  `heard_about_us_id` int(11) DEFAULT NULL,
  `primhd_referral_no` varchar(50) DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `is_gp_only` tinyint(4) NOT NULL,
  `referral_to_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `peer_id_idx` (`peer_id`),
  KEY `ps_number_id_idx` (`ps_number_id`),
  KEY `service_organisation_id_idx` (`service_organisation_id`),
  KEY `key_worker_id_idx` (`key_worker_id`),
  KEY `iss_clinical_service_id_idx` (`iss_clinical_service_id`),
  KEY `iss_psychiatrist_id_idx` (`iss_psychiatrist_id`),
  KEY `iss_service_coordinator_id_idx` (`iss_service_coordinator_id`),
  KEY `referral_source_id_idx` (`referral_source_id`),
  KEY `iss_referred_by_idx` (`iss_referred_by`),
  KEY `record_entered_by_idx` (`record_entered_by`),
  KEY `waiting_psw_id_idx` (`waiting_psw_id`),
  KEY `waiting_list_priority_id_idx` (`waiting_list_priority_id`),
  KEY `assigned_psw_id_idx` (`assigned_psw_id`),
  KEY `assigned_by_idx` (`assigned_by`),
  KEY `last_reengaged_id_idx` (`last_reengaged_id`),
  KEY `disengagement_type_id_idx` (`disengagement_type_id`),
  KEY `status_id_idx` (`status_id`),
  KEY `closed_by_idx` (`closed_by`),
  KEY `peer_engagement_heard_about_us_id_fk` (`heard_about_us_id`),
  KEY `moved_to_and_delete_id_foreign_key` (`moved_to_and_delete_id`),
  KEY `deleted_date` (`deleted_date`),
  KEY `disengagement_target` (`disengagement_target`),
  KEY `peer_engagement_referral_to_id_fk` (`referral_to_id`),
  KEY `is_intensive` (`is_intensive`),
  KEY `is_intensive_2` (`is_intensive`,`status_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_number`
--

DROP TABLE IF EXISTS `peer_number`;
CREATE TABLE IF NOT EXISTS `peer_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ps_number` varchar(5) NOT NULL,
  `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `contract_id_idx` (`contract_id`),
  KEY `ps_number` (`ps_number`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75981 ;

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `suite` varchar(100) DEFAULT NULL,
  `hnr_street` varchar(200) NOT NULL,
  `suburb` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postcode` varchar(5) NOT NULL,
  `manual_address_entry` tinyint(1) DEFAULT NULL,
  `current_living_situation_id` int(11) DEFAULT NULL,
  `dhb_area_id` int(11) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone_daytime` varchar(25) DEFAULT NULL,
  `phone_evening` varchar(25) DEFAULT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `emergency_contact_details` text,
  `notes` mediumtext,
  `dob` date DEFAULT NULL,
  `nhi` varchar(7) DEFAULT NULL,
  `gender_id` int(11) DEFAULT NULL,
  `ethnicity_id` int(11) DEFAULT NULL,
  `team_leader_id` int(11) DEFAULT NULL,
  `organisation_id` int(11) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `postal_address` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `employment_situation_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `current_living_situation_id_idx` (`current_living_situation_id`),
  KEY `dhb_area_id_idx` (`dhb_area_id`),
  KEY `gender_id_idx` (`gender_id`),
  KEY `team_leader_id_idx` (`team_leader_id`),
  KEY `organisation_id_idx` (`organisation_id`),
  KEY `moved_to_and_delete_id_foreign_key_person` (`moved_to_and_delete_id`),
  KEY `person_employment_situation_id_fk` (`employment_situation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2377 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contract`
--
ALTER TABLE `contract`
  ADD CONSTRAINT `contract_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`);

--
-- Constraints for table `peer_engagement`
--
ALTER TABLE `peer_engagement`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `peer_engagement` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_by_person_id` FOREIGN KEY (`assigned_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_psw_id_person_id` FOREIGN KEY (`assigned_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_closed_by_person_id` FOREIGN KEY (`closed_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_disengagement_type_id_disengagement_type_id` FOREIGN KEY (`disengagement_type_id`) REFERENCES `disengagement_type` (`id`),
  ADD CONSTRAINT `peer_engagement_heard_about_us_id_fk` FOREIGN KEY (`heard_about_us_id`) REFERENCES `heard_about_us` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_clinical_service_id_organisation_id` FOREIGN KEY (`iss_clinical_service_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_psychiatrist_id_person_id` FOREIGN KEY (`iss_psychiatrist_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_referred_by_person_id` FOREIGN KEY (`iss_referred_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_service_coordinator_id_person_id` FOREIGN KEY (`iss_service_coordinator_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_key_worker_id_person_id` FOREIGN KEY (`key_worker_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_last_reengaged_id_peer_number_id` FOREIGN KEY (`last_reengaged_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_peer_id_person_id` FOREIGN KEY (`peer_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_ps_number_id_peer_number_id` FOREIGN KEY (`ps_number_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_record_entered_by_person_id` FOREIGN KEY (`record_entered_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_source_id_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_source` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_to_id_fk` FOREIGN KEY (`referral_to_id`) REFERENCES `referral_to` (`id`),
  ADD CONSTRAINT `peer_engagement_service_organisation_id_organisation_id` FOREIGN KEY (`service_organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_status_id_status_id` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  ADD CONSTRAINT `peer_engagement_waiting_psw_id_person_id` FOREIGN KEY (`waiting_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `pwwi` FOREIGN KEY (`waiting_list_priority_id`) REFERENCES `waiting_list_priority` (`id`);

--
-- Constraints for table `peer_number`
--
ALTER TABLE `peer_number`
  ADD CONSTRAINT `peer_number_contract_id_contract_id` FOREIGN KEY (`contract_id`) REFERENCES `contract` (`id`);

--
-- Constraints for table `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key_person` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `person_current_living_situation_id_current_living_situation_id` FOREIGN KEY (`current_living_situation_id`) REFERENCES `current_living_situation` (`id`),
  ADD CONSTRAINT `person_dhb_area_id_contract_id` FOREIGN KEY (`dhb_area_id`) REFERENCES `contract` (`id`),
  ADD CONSTRAINT `person_employment_situation_id_fk` FOREIGN KEY (`employment_situation_id`) REFERENCES `employment_situation` (`id`),
  ADD CONSTRAINT `person_gender_id_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`),
  ADD CONSTRAINT `person_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `person_team_leader_id_person_id` FOREIGN KEY (`team_leader_id`) REFERENCES `person` (`id`);

Consulta 1:

1) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, COALESCE(p2.name, p3.name) AS p2__0 
  FROM peer_engagement p 
    LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
    LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
    INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
    INNER JOIN contract c ON p4.contract_id = c.id 
    INNER JOIN person p5 ON p.peer_id = p5.id 
  WHERE (p.is_intensive = 0 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    611 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1   
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1   
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1

Consulta 2:

2) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, COALESCE(p2.name, p3.name) AS p2__0 
  FROM peer_engagement p 
    LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
    LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
    INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
    INNER JOIN contract c ON p4.contract_id = c.id 
    INNER JOIN person p5 ON p.peer_id = p5.id 
  WHERE (p.is_intensive = 1 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    128 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1   
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1   
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1

A diferença está apenas na is_intensivecoluna.

Aqui está a divisão dos dados :

SELECT is_intensive, status_id, COUNT( * ) 
FROM  `peer_engagement` 
GROUP BY is_intensive, status_id
ORDER BY is_intensive, status_id

is_intensive    status_id   count(*)
0   1   18
0   3   229
0   4   13
0   5   101
0   6   653
0   7   252
1   1   2
1   3   26
1   5   73
1   6   14
1   7   24

Para minha surpresa, a primeira consulta é significativamente acelerada por index is_intensive_2, mas a segunda não. Eu esperava o contrário: a 1ª consulta abrange mais de 50% da tabela, portanto, uma verificação da tabela é executada; no entanto, para a 2ª consulta, o índice seria usado.

Estou procurando uma maneira de fazer ambas as consultas rápidas, ou seja, em uma magnitude como a primeira consulta funciona agora.

automatem

Asked: 2015-09-09 13:55:50 +0800 CST

otimizar my.ini para consulta de chave

1

Eu tenho um sistema de banco de dados com uma consulta de painel bastante complexa. Primeiro de tudo os detalhes.

Modelo de dados:

DROP TABLE IF EXISTS `contract`;
CREATE TABLE IF NOT EXISTS `contract` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) NOT NULL,
  `code` varchar(3) NOT NULL,
  `moh_code` varchar(20) NOT NULL,
  `moh_number` varchar(20) NOT NULL,
  `moh_variation` varchar(20) NOT NULL,
  `description` mediumtext NOT NULL,
  `start` datetime NOT NULL,
  `finish` datetime NOT NULL,
  `cities` text,
  `is_support_contract` tinyint(1) DEFAULT NULL,
  `is_intensive` tinyint(1) DEFAULT NULL,
  `moh_team_type` varchar(4) DEFAULT NULL,
  `moh_team_setting` varchar(1) DEFAULT NULL,
  `moh_service_type` varchar(2) DEFAULT NULL,
  `moh_target_population` int(4) DEFAULT NULL,
  `moh_facility_id` varchar(10) DEFAULT NULL,
  `moh_open_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `organisation_id_idx` (`organisation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_engagement`
--

DROP TABLE IF EXISTS `peer_engagement`;
CREATE TABLE IF NOT EXISTS `peer_engagement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `peer_id` int(11) NOT NULL,
  `ps_number_id` int(11) NOT NULL,
  `service_organisation_id` int(11) DEFAULT NULL,
  `past_service_disengaged` datetime DEFAULT NULL,
  `key_worker_id` int(11) DEFAULT NULL,
  `iss_clinical_service_id` int(11) DEFAULT NULL,
  `iss_psychiatrist_id` int(11) DEFAULT NULL,
  `iss_service_coordinator_id` int(11) DEFAULT NULL,
  `iss_declined_courier_back_address` varchar(255) DEFAULT NULL,
  `waitinglist_support_suggestions_made` mediumtext,
  `waiting_psw_id` int(11) DEFAULT NULL,
  `declined_waiting_what_support_avail` mediumtext,
  `referral_source_id` int(11) DEFAULT NULL,
  `iss_referred_by` int(11) DEFAULT NULL,
  `record_entered_by` int(11) NOT NULL,
  `record_entered` datetime NOT NULL,
  `waiting_list_priority_id` int(11) DEFAULT NULL,
  `assigned_psw_id` int(11) DEFAULT NULL,
  `assigned` datetime DEFAULT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `assigned_effective` datetime DEFAULT NULL,
  `last_reengaged` datetime DEFAULT NULL,
  `last_reengaged_id` int(11) DEFAULT NULL,
  `disengagement_started` datetime DEFAULT NULL,
  `disengagement_target` datetime DEFAULT NULL,
  `disengagement_type_id` int(11) DEFAULT NULL,
  `disengagement_comments` mediumtext,
  `status_id` int(11) DEFAULT NULL,
  `closed` datetime DEFAULT NULL,
  `closed_by` int(11) DEFAULT NULL,
  `is_intensive` varchar(5) DEFAULT NULL,
  `heard_about_us_id` int(11) DEFAULT NULL,
  `primhd_referral_no` varchar(50) DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `is_gp_only` tinyint(4) NOT NULL,
  `referral_to_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `peer_id_idx` (`peer_id`),
  KEY `ps_number_id_idx` (`ps_number_id`),
  KEY `service_organisation_id_idx` (`service_organisation_id`),
  KEY `key_worker_id_idx` (`key_worker_id`),
  KEY `iss_clinical_service_id_idx` (`iss_clinical_service_id`),
  KEY `iss_psychiatrist_id_idx` (`iss_psychiatrist_id`),
  KEY `iss_service_coordinator_id_idx` (`iss_service_coordinator_id`),
  KEY `referral_source_id_idx` (`referral_source_id`),
  KEY `iss_referred_by_idx` (`iss_referred_by`),
  KEY `record_entered_by_idx` (`record_entered_by`),
  KEY `waiting_psw_id_idx` (`waiting_psw_id`),
  KEY `waiting_list_priority_id_idx` (`waiting_list_priority_id`),
  KEY `assigned_psw_id_idx` (`assigned_psw_id`),
  KEY `assigned_by_idx` (`assigned_by`),
  KEY `last_reengaged_id_idx` (`last_reengaged_id`),
  KEY `disengagement_type_id_idx` (`disengagement_type_id`),
  KEY `status_id_idx` (`status_id`),
  KEY `closed_by_idx` (`closed_by`),
  KEY `peer_engagement_heard_about_us_id_fk` (`heard_about_us_id`),
  KEY `moved_to_and_delete_id_foreign_key` (`moved_to_and_delete_id`),
  KEY `deleted_date` (`deleted_date`),
  KEY `disengagement_target` (`disengagement_target`),
  KEY `peer_engagement_referral_to_id_fk` (`referral_to_id`),
  KEY `is_intensive` (`is_intensive`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_number`
--

DROP TABLE IF EXISTS `peer_number`;
CREATE TABLE IF NOT EXISTS `peer_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ps_number` varchar(5) NOT NULL,
  `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `contract_id_idx` (`contract_id`),
  KEY `ps_number` (`ps_number`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75981 ;

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `suite` varchar(100) DEFAULT NULL,
  `hnr_street` varchar(200) NOT NULL,
  `suburb` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postcode` varchar(5) NOT NULL,
  `manual_address_entry` tinyint(1) DEFAULT NULL,
  `current_living_situation_id` int(11) DEFAULT NULL,
  `dhb_area_id` int(11) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone_daytime` varchar(25) DEFAULT NULL,
  `phone_evening` varchar(25) DEFAULT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `emergency_contact_details` text,
  `notes` mediumtext,
  `dob` date DEFAULT NULL,
  `nhi` varchar(7) DEFAULT NULL,
  `gender_id` int(11) DEFAULT NULL,
  `ethnicity_id` int(11) DEFAULT NULL,
  `team_leader_id` int(11) DEFAULT NULL,
  `organisation_id` int(11) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `postal_address` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `employment_situation_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `current_living_situation_id_idx` (`current_living_situation_id`),
  KEY `dhb_area_id_idx` (`dhb_area_id`),
  KEY `gender_id_idx` (`gender_id`),
  KEY `team_leader_id_idx` (`team_leader_id`),
  KEY `organisation_id_idx` (`organisation_id`),
  KEY `moved_to_and_delete_id_foreign_key_person` (`moved_to_and_delete_id`),
  KEY `person_employment_situation_id_fk` (`employment_situation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2377 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contract`
--
ALTER TABLE `contract`
  ADD CONSTRAINT `contract_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`);

--
-- Constraints for table `peer_engagement`
--
ALTER TABLE `peer_engagement`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `peer_engagement` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_by_person_id` FOREIGN KEY (`assigned_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_psw_id_person_id` FOREIGN KEY (`assigned_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_closed_by_person_id` FOREIGN KEY (`closed_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_disengagement_type_id_disengagement_type_id` FOREIGN KEY (`disengagement_type_id`) REFERENCES `disengagement_type` (`id`),
  ADD CONSTRAINT `peer_engagement_heard_about_us_id_fk` FOREIGN KEY (`heard_about_us_id`) REFERENCES `heard_about_us` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_clinical_service_id_organisation_id` FOREIGN KEY (`iss_clinical_service_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_psychiatrist_id_person_id` FOREIGN KEY (`iss_psychiatrist_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_referred_by_person_id` FOREIGN KEY (`iss_referred_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_service_coordinator_id_person_id` FOREIGN KEY (`iss_service_coordinator_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_key_worker_id_person_id` FOREIGN KEY (`key_worker_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_last_reengaged_id_peer_number_id` FOREIGN KEY (`last_reengaged_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_peer_id_person_id` FOREIGN KEY (`peer_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_ps_number_id_peer_number_id` FOREIGN KEY (`ps_number_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_record_entered_by_person_id` FOREIGN KEY (`record_entered_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_source_id_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_source` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_to_id_fk` FOREIGN KEY (`referral_to_id`) REFERENCES `referral_to` (`id`),
  ADD CONSTRAINT `peer_engagement_service_organisation_id_organisation_id` FOREIGN KEY (`service_organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_status_id_status_id` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  ADD CONSTRAINT `peer_engagement_waiting_psw_id_person_id` FOREIGN KEY (`waiting_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `pwwi` FOREIGN KEY (`waiting_list_priority_id`) REFERENCES `waiting_list_priority` (`id`);

--
-- Constraints for table `peer_number`
--
ALTER TABLE `peer_number`
  ADD CONSTRAINT `peer_number_contract_id_contract_id` FOREIGN KEY (`contract_id`) REFERENCES `contract` (`id`);

--
-- Constraints for table `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key_person` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `person_current_living_situation_id_current_living_situation_id` FOREIGN KEY (`current_living_situation_id`) REFERENCES `current_living_situation` (`id`),
  ADD CONSTRAINT `person_dhb_area_id_contract_id` FOREIGN KEY (`dhb_area_id`) REFERENCES `contract` (`id`),
  ADD CONSTRAINT `person_employment_situation_id_fk` FOREIGN KEY (`employment_situation_id`) REFERENCES `employment_situation` (`id`),
  ADD CONSTRAINT `person_gender_id_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`),
  ADD CONSTRAINT `person_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `person_team_leader_id_person_id` FOREIGN KEY (`team_leader_id`) REFERENCES `person` (`id`);

Consulta:

SELECT p.id AS p__id, p.peer_id AS p__peer_id, p.ps_number_id AS p__ps_number_id, p.service_organisation_id AS p__service_organisation_id, p.past_service_disengaged AS p__past_service_disengaged, p.key_worker_id AS p__key_worker_id, p.is_gp_only AS p__is_gp_only, p.iss_clinical_service_id AS p__iss_clinical_service_id, p.iss_psychiatrist_id AS p__iss_psychiatrist_id, p.iss_service_coordinator_id AS p__iss_service_coordinator_id, p.iss_declined_courier_back_address AS p__iss_declined_courier_back_address, p.waitinglist_support_suggestions_made AS p__waitinglist_support_suggestions_made, p.waiting_psw_id AS p__waiting_psw_id, p.declined_waiting_what_support_avail AS p__declined_waiting_what_support_avail, p.referral_source_id AS p__referral_source_id, p.iss_referred_by AS p__iss_referred_by, p.record_entered_by AS p__record_entered_by, p.record_entered AS p__record_entered, p.waiting_list_priority_id AS p__waiting_list_priority_id, p.assigned_psw_id AS p__assigned_psw_id, p.assigned AS p__assigned, p.assigned_by AS p__assigned_by, p.assigned_effective AS p__assigned_effective, p.last_reengaged AS p__last_reengaged, p.last_reengaged_id AS p__last_reengaged_id, p.disengagement_started AS p__disengagement_started, p.disengagement_target AS p__disengagement_target, p.disengagement_type_id AS p__disengagement_type_id, p.disengagement_comments AS p__disengagement_comments, p.status_id AS p__status_id, p.closed AS p__closed, p.closed_by AS p__closed_by, p.is_intensive AS p__is_intensive, p.heard_about_us_id AS p__heard_about_us_id, p.primhd_referral_no AS p__primhd_referral_no, p.moved_to_and_delete_id AS p__moved_to_and_delete_id, p.deleted_date AS p__deleted_date, p.referral_to_id AS p__referral_to_id, p2.id AS p2__id, p2.user_id AS p2__user_id, p2.name AS p2__name, p2.suite AS p2__suite, p2.hnr_street AS p2__hnr_street, p2.suburb AS p2__suburb, p2.city AS p2__city, p2.postcode AS p2__postcode, p2.manual_address_entry AS p2__manual_address_entry, p2.postal_address AS p2__postal_address, p2.current_living_situation_id AS p2__current_living_situation_id, p2.employment_situation_id AS p2__employment_situation_id, p2.dhb_area_id AS p2__dhb_area_id, p2.email AS p2__email, p2.phone_daytime AS p2__phone_daytime, p2.phone_evening AS p2__phone_evening, p2.mobile AS p2__mobile, p2.fax AS p2__fax, p2.emergency_contact_details AS p2__emergency_contact_details, p2.notes AS p2__notes, p2.dob AS p2__dob, p2.nhi AS p2__nhi, p2.gender_id AS p2__gender_id, p2.team_leader_id AS p2__team_leader_id, p2.start_date AS p2__start_date, p2.organisation_id AS p2__organisation_id, p2.role AS p2__role, p2.type AS p2__type, p2.moved_to_and_delete_id AS p2__moved_to_and_delete_id, p2.deleted_date AS p2__deleted_date, p3.id AS p3__id, p3.user_id AS p3__user_id, p3.name AS p3__name, p3.suite AS p3__suite, p3.hnr_street AS p3__hnr_street, p3.suburb AS p3__suburb, p3.city AS p3__city, p3.postcode AS p3__postcode, p3.manual_address_entry AS p3__manual_address_entry, p3.postal_address AS p3__postal_address, p3.current_living_situation_id AS p3__current_living_situation_id, p3.employment_situation_id AS p3__employment_situation_id, p3.dhb_area_id AS p3__dhb_area_id, p3.email AS p3__email, p3.phone_daytime AS p3__phone_daytime, p3.phone_evening AS p3__phone_evening, p3.mobile AS p3__mobile, p3.fax AS p3__fax, p3.emergency_contact_details AS p3__emergency_contact_details, p3.notes AS p3__notes, p3.dob AS p3__dob, p3.nhi AS p3__nhi, p3.gender_id AS p3__gender_id, p3.team_leader_id AS p3__team_leader_id, p3.start_date AS p3__start_date, p3.organisation_id AS p3__organisation_id, p3.role AS p3__role, p3.type AS p3__type, p3.moved_to_and_delete_id AS p3__moved_to_and_delete_id, p3.deleted_date AS p3__deleted_date, p4.id AS p4__id, p4.ps_number AS p4__ps_number, p4.contract_id AS p4__contract_id, c.id AS c__id, c.organisation_id AS c__organisation_id, c.code AS c__code, c.moh_code AS c__moh_code, c.moh_team_type AS c__moh_team_type, c.moh_team_setting AS c__moh_team_setting, c.moh_service_type AS c__moh_service_type, c.moh_target_population AS c__moh_target_population, c.moh_facility_id AS c__moh_facility_id, c.moh_open_date AS c__moh_open_date, c.moh_number AS c__moh_number, c.moh_variation AS c__moh_variation, c.description AS c__description, c.start AS c__start, c.finish AS c__finish, c.cities AS c__cities, c.is_support_contract AS c__is_support_contract, c.is_intensive AS c__is_intensive, COALESCE(p2.name, p3.name) AS p2__0 
FROM peer_engagement p 
LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
INNER JOIN contract c ON p4.contract_id = c.id 
INNER JOIN person p5 ON p.peer_id = p5.id 
WHERE (p.is_intensive = 1 AND p.status_id in (0,1,2,3,4,5,7)

Explique a declaração

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   ref peer_id_idx,ps_number_id_idx,status_id_idx,is_intensive,is_intensive_2  is_intensive    8   const   139 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1

A configuração é padrão Ubuntu 12.04:

auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
back_log    50
basedir /usr
big_tables  OFF
binlog_cache_size   32768
binlog_direct_non_transactional_updates OFF
binlog_format   STATEMENT
binlog_stmt_cache_size  32768
bulk_insert_buffer_size 8388608
character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
completion_type NO_CHAIN
concurrent_insert   AUTO
connect_timeout 10
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine  InnoDB
default_week_format 0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
div_precision_increment 4
engine_condition_pushdown   ON
error_count 0
event_scheduler OFF
expire_logs_days    10
external_user   
flush   OFF
flush_time  0
foreign_key_checks  ON
ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
general_log OFF
general_log_file    /var/lib/mysql/mindandbody.log
group_concat_max_len    1024
have_compress   YES
have_crypt  YES
have_csv    YES
have_dynamic_loading    YES
have_geometry   YES
have_innodb YES
have_ndbcluster NO
have_openssl    DISABLED
have_partitioning   YES
have_profiling  YES
have_query_cache    YES
have_rtree_keys YES
have_ssl    DISABLED
have_symlink    YES
hostname    mindandbody
identity    0
ignore_builtin_innodb   OFF
init_connect    
init_file   
init_slave  
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 134217728
innodb_change_buffering all
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_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method 
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
Variable_name   Value
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_print_all_deadlocks  OFF
innodb_purge_batch_size 20
innodb_purge_threads    0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  4
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method nulls_equal
innodb_stats_on_metadata    ON
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.5.37
innodb_write_io_threads 4
insert_id   0
interactive_timeout 28800
join_buffer_size    131072
keep_files_on_create    OFF
key_buffer_size 16777216
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id  0
lc_messages en_US
lc_messages_dir /usr/share/mysql/english/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error   
log_output  FILE
log_queries_not_using_indexes   ON
log_slave_updates   OFF
log_slow_queries    OFF
log_warnings    1
long_query_time 10.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet  16777216
max_binlog_cache_size   18446744073709547520
max_binlog_size 104857600
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  10
max_connections 60
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads  20
max_join_size   18446744073709551615
max_length_for_sort_data    1024
max_long_data_size  16777216
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   18446744073709551615
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    18446744073709551615
metadata_locks_cache_size   1024
min_examined_row_limit  0
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   9223372036853727232
myisam_mmap_size    18446744073709551615
myisam_recover_options  BACKUP
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length   16384
net_read_timeout    30
net_retry_count 10
net_write_timeout   60
Variable_name   Value
new OFF
old OFF
old_alter_table OFF
old_passwords   OFF
open_files_limit    1024
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch    index_merge=on,index_merge_union=on,index_merge_so...
performance_schema  OFF
performance_schema_events_waits_history_long_size   10000
performance_schema_events_waits_history_size    10
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances   1000
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances   10000
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  1000000
performance_schema_max_rwlock_classes   30
performance_schema_max_rwlock_instances 1000000
performance_schema_max_table_handles    100000
performance_schema_max_table_instances  50000
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances 1000
pid_file    /var/run/mysqld/mysqld.pid
plugin_dir  /usr/lib/mysql/plugin/
port    3306
preload_buffer_size 32768
profiling   OFF
profiling_history_size  15
protocol_version    10
proxy_user  
pseudo_slave_mode   OFF
pseudo_thread_id    205661
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    16777216
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size 8192
rand_seed1  0
rand_seed2  0
range_alloc_block_size  4096
read_buffer_size    131072
read_only   OFF
read_rnd_buffer_size    262144
relay_log   
relay_log_index 
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_recovery  OFF
relay_log_space_limit   0
report_host 
report_password 
report_port 3306
report_user 
rpl_recovery_rank   0
secure_auth OFF
secure_file_priv    
server_id   0
skip_external_locking   ON
skip_name_resolve   OFF
skip_networking OFF
skip_show_database  OFF
slave_compressed_protocol   OFF
slave_exec_mode STRICT
slave_load_tmpdir   /tmp
slave_max_allowed_packet    1073741824
slave_net_timeout   3600
slave_skip_errors   OFF
slave_transaction_retries   10
slave_type_conversions  
slow_launch_time    2
slow_query_log  OFF
slow_query_log_file /var/lib/mysql/mindandbody-slow.log
socket  /var/run/mysqld/mysqld.sock
sort_buffer_size    2097152
sql_auto_is_null    OFF
sql_big_selects ON
sql_big_tables  OFF
sql_buffer_result   OFF
sql_log_bin ON
sql_log_off OFF
sql_low_priority_updates    OFF
sql_max_join_size   18446744073709551615
sql_mode    
sql_notes   ON
sql_quote_show_create   ON
sql_safe_updates    OFF
sql_select_limit    18446744073709551615
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca  
ssl_capath  
ssl_cert    
ssl_cipher  
ssl_key 
storage_engine  InnoDB
stored_program_cache    256
Variable_name   Value
sync_binlog 0
sync_frm    ON
sync_master_info    0
sync_relay_log  0
sync_relay_log_info 0
system_time_zone    NZDT
table_definition_cache  400
table_open_cache    256
thread_cache_size   8
thread_concurrency  10
thread_handling one-thread-per-connection
thread_stack    196608
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
timestamp   1441749665
tmp_table_size  16777216
tmpdir  /tmp
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit  YES
version 5.5.37-0ubuntu0.12.04.1
version_comment (Ubuntu)
version_compile_machine x86_64
version_compile_os  debian-linux-gnu
wait_timeout    28800
warning_count   0

Como posso determinar se alguma das configurações do my.ini (ou a falta delas) reduz desnecessariamente a velocidade da minha consulta?

automatem

Asked: 2014-11-07 18:25:00 +0800 CST

Consulta duplicada de intervalo de otimização do MySQL

0

Preciso eliminar duplicatas de uma planilha de horas. Eu encontrei esta solução e a adaptei para minhas próprias necessidades:

DROP TABLE IF EXISTS `activity`;
CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `planned_start` datetime DEFAULT NULL,
  `planned_end` datetime DEFAULT NULL,
  `actual_start` datetime DEFAULT NULL,
  `actual_end` datetime DEFAULT NULL,
  `code_id` int(11) DEFAULT NULL,
  `setting_id` int(11) DEFAULT NULL,
  `notes` text,
  `travel_distance` decimal(8,2) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `submitted` tinyint(1) DEFAULT NULL,
  `approved` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `peer_engagement_id` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  `travel_notes` varchar(8000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `code_id_idx` (`code_id`),
  KEY `setting_id_idx` (`setting_id`),
  KEY `created_by_idx` (`created_by`),
  KEY `updated_by_idx` (`updated_by`),
  KEY `approved_by_idx` (`approved_by`),
  KEY `activity_peer_engagement_id_fk` (`peer_engagement_id`),
  KEY `activity_person_id_fk` (`person_id`),
  KEY `actual_start` (`actual_start`,`actual_end`),
  KEY `created` (`created`),
  KEY `person_id` (`person_id`,`actual_start`,`actual_end`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=165796 ;


SELECT
  COUNT(*) as occurrence
  , sub.id
  , SEC_TO_TIME(SUM(
      IF(a2start > a1start, a1end - a2start, a2end - a1start))) as duration
FROM
  (  SELECT
       a1.id
      , UNIX_TIMESTAMP(a1.actual_start) as a1start
      , UNIX_TIMESTAMP(a1.actual_end) as a1end
      , UNIX_TIMESTAMP(a2.actual_start) as a2start
      , UNIX_TIMESTAMP(a2.actual_end) as a2end
    FROM activity a1
    INNER JOIN activity a2
      ON (a1.id <> a2.id and a1.person_id=a2.person_id
      AND NOT(a1.actual_start > a2.actual_end OR a1.actual_end < a2.actual_start))
  ) sub

O problema é que não consigo nem executar a explicação na minha consulta, meu servidor mysql entra em 100% de uso da CPU e parece ficar lá por minutos.

Posso executar a explicação na consulta interna:

explain SELECT
   a1.id
  , UNIX_TIMESTAMP(a1.actual_start) as a1start
  , UNIX_TIMESTAMP(a1.actual_end) as a1end
  , UNIX_TIMESTAMP(a2.actual_start) as a2start
  , UNIX_TIMESTAMP(a2.actual_end) as a2end
FROM activity a1
INNER JOIN activity a2
  ON (a1.id <> a2.id and a1.person_id=a2.person_id
  AND NOT(a1.actual_start > a2.actual_end OR a1.actual_end < a2.actual_start))

+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table | type  | possible_keys                                | key       | key_len | ref                                  | rows   | Extra                    |
+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
|  1 | SIMPLE      | a1    | index | activity_person_id_fk,actual_start,person_id | person_id | 23      | NULL                                 | 176586 | Using index              |
|  1 | SIMPLE      | a2    | ref   | activity_person_id_fk,actual_start,person_id | person_id | 5       | mabel_mindandbody_co_nz.a1.person_id |  19705 | Using where; Using index |
+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
2 rows in set (0.00 sec)

Minhas perguntas:

Por que não explica o trabalho aqui?
Como posso otimizar essa consulta para fornecer resultados de velocidade aceitáveis?

Em relação à otimização - não consigo encontrar nada além dos índices que já usei em minha tabela.

Uma outra opção que pensei é adicionar um campo adicional, codificando todos os dias em um número. Eu sei que uma entrada na planilha de horas nunca dura mais de 24 horas e tenho certeza de que excluir as entradas da planilha de horas que abrangem a meia-noite seria aceitável. Portanto, com isso, espero usar um índice menor nesta coluna adicional na consulta interna.

automatem

Asked: 2013-03-26 17:37:37 +0800 CST

Acelere a consulta eficiente para a primeira associação, a última categoria da tabela de associação (mín., máx.)

0

Eu tenho a seguinte tabela representando informações de associação:

    CREATE TABLE IF NOT EXISTS `membership` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `organisation_id` int(11) NOT NULL,
      `membership_subcategory_id` int(11) NOT NULL,
      `start` datetime DEFAULT NULL,
      `end` datetime DEFAULT NULL,
      `amount` decimal(9,2) DEFAULT NULL,
      `amount_paid` decimal(9,2) DEFAULT NULL,
      `notes` mediumtext,
      `order_id` int(11) DEFAULT NULL,
      `payment_type` varchar(20) NOT NULL,
      `active` tinyint(4) NOT NULL DEFAULT '1',
      `cancelled` tinyint(4) NOT NULL DEFAULT '0',
      `cancelled_date` datetime DEFAULT NULL,
      `cancellation_reason` mediumtext,
      `certificate_sent` date DEFAULT NULL,
      `welcome_email_sent` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`),
      UNIQUE KEY `order_id_2` (`order_id`,`start`,`end`,`organisation_id`),
      KEY `membership_subcategory_id_idx` (`membership_subcategory_id`),
      KEY `organisation_id_idx` (`organisation_id`),
      KEY `order_id` (`order_id`)
    )

Organization_id é um membro
o ano da associação vai de 1º de julho a 30 de junho, inicie os registros quando a associação de cada ano começar - isso pode ser em qualquer lugar no primeiro ano, mas é sempre 1º de julho, a menos que um ano seja pulado
Membership_subcategory_id é uma categoria do setor à qual a associação se aplica a cada ano.

Preciso de uma consulta eficiente para obter a data de ingresso e a última categoria de associação.

Eu tentei esta consulta, mas recebo "Uso inválido da função de grupo" como um erro

SELECT m.organisation_id, m2.membership_subcategory_id, MIN( m.start ) 
FROM membership m
INNER JOIN membership m2 ON m.organisation_id = m2.organisation_id
WHERE MAX( m.start ) = m2.start
GROUP BY m.organisation_id, m2.membership_subcategory_id

automatem

Asked: 2013-02-22 13:28:33 +0800 CST

Atualização para tabela de sessão lenta

4

Eu tenho vários sites com tabelas de sessão em um servidor da web, que são usados pelos sistemas de estrutura Joomla CMS e Symfony. Com slow_query_log=0.6, as consultas de atualização para as tabelas de sessão aparecem regularmente no log lento.

A definição da tabela de sessão do Joomla é

    CREATE TABLE IF NOT EXISTS `jos_session` (
      `username` varchar(150) DEFAULT '',
      `time` varchar(14) DEFAULT '',
      `session_id` varchar(200) NOT NULL DEFAULT '0',
      `guest` tinyint(4) DEFAULT '1',
      `userid` int(11) DEFAULT '0',
      `usertype` varchar(50) DEFAULT '',
      `gid` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `client_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `data` longtext,
      PRIMARY KEY (`session_id`(64)),
      KEY `whosonline` (`guest`,`usertype`),
      KEY `userid` (`userid`),
      KEY `time` (`time`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Eu tentei mudar o mecanismo de armazenamento para InnoDB sem efeito, tentei otimizar a tabela e também removi todo o índice, mas o principal. Existem registros nesta tabela com espaço de armazenamento

Data    12,352.0    KiB
Index   144.0   KiB
Total   12,496.0    KiB

Configuração do meu servidor

        auto_increment_increment                   1
    auto_increment_offset                      1
    autocommit                       ON         
    automatic_sp_privileges          ON         
    back_log                                  50
    basedir                          /usr       
    big_tables                       OFF        
    binlog_cache_size                      32768
    binlog_direct_non_transactional_uOFF        
    binlog_format                    STATEMENT  
    binlog_stmt_cache_size                 32768
    bulk_insert_buffer_size              8388608
    character_set_client             utf8       
    character_set_connection         utf8       
    character_set_database           latin1     
    character_set_filesystem         binary     
    character_set_results            utf8       
    character_set_server             latin1     
    character_set_system             utf8       
    character_sets_dir               /usr/share/
    collation_connection             utf8_genera
    collation_database               latin1_swed
    collation_server                 latin1_swed
    completion_type                  NO_CHAIN   
    concurrent_insert                AUTO       
    connect_timeout                           10
    datadir                          /var/lib/my
    date_format                      %Y-%m-%d   
    datetime_format                  %Y-%m-%d %H
    default_storage_engine           InnoDB     
    default_week_format                        0
    delay_key_write                  ON         
    delayed_insert_limit                     100
    delayed_insert_timeout                   300
    delayed_queue_size                      1000
    div_precision_increment                    4
    engine_condition_pushdown        ON         
    error_count                                0
    event_scheduler                  OFF        
    expire_logs_days                          10
    external_user                    
    flush                            OFF        
    flush_time                                 0
    foreign_key_checks               ON         
    ft_boolean_syntax                + -><()~*:"
    ft_max_word_len                           84
    ft_min_word_len                            4
    ft_query_expansion_limit                  20
    ft_stopword_file                 (built-in) 
    general_log                      OFF        
    general_log_file                 /var/lib/my
    group_concat_max_len                    1024
    have_compress                    YES        
    have_crypt                       YES        
    have_csv                         YES        
    have_dynamic_loading             YES        
    have_geometry                    YES        
    have_innodb                      YES        
    have_ndbcluster                  NO         
    have_openssl                     DISABLED   
    have_partitioning                YES        
    have_profiling                   YES        
    have_query_cache                 YES        
    have_rtree_keys                  YES        
    have_ssl                         DISABLED   
    have_symlink                     YES        
    hostname                         nzs2.automa
    identity                                   0
    ignore_builtin_innodb            OFF        
    init_connect                     
    init_file                        
    init_slave                       
    innodb_adaptive_flushing         ON         
    innodb_adaptive_hash_index       ON         
    innodb_additional_mem_pool_size      8388608
    innodb_autoextend_increment                8
    innodb_autoinc_lock_mode                   1
    innodb_buffer_pool_instances               1
    innodb_buffer_pool_size            268435456
    innodb_change_buffering          all        
    innodb_checksums                 ON         
    innodb_commit_concurrency                  0
    innodb_concurrency_tickets               500
    innodb_data_file_path            ibdata1:10M
    innodb_data_home_dir             
    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            OFF        
    innodb_flush_log_at_trx_commit             1
    innodb_flush_method              
    innodb_force_load_corrupted      OFF        
    innodb_force_recovery                      0
    innodb_io_capacity                       200
    innodb_large_prefix              OFF        
    innodb_lock_wait_timeout                  50
    innodb_locks_unsafe_for_binlog   OFF        
    innodb_log_buffer_size               8388608
              Variable_name             Value   
    innodb_log_file_size                 5242880
    innodb_log_files_in_group                  2
    innodb_log_group_home_dir        ./         
    innodb_max_dirty_pages_pct                75
    innodb_max_purge_lag                       0
    innodb_mirrored_log_groups                 1
    innodb_old_blocks_pct                     37
    innodb_old_blocks_time                     0
    innodb_open_files                        300
    innodb_purge_batch_size                   20
    innodb_purge_threads                       0
    innodb_random_read_ahead         OFF        
    innodb_read_ahead_threshold               56
    innodb_read_io_threads                     4
    innodb_replication_delay                   0
    innodb_rollback_on_timeout       OFF        
    innodb_rollback_segments                 128
    innodb_spin_wait_delay                     6
    innodb_stats_method              nulls_equal
    innodb_stats_on_metadata         ON         
    innodb_stats_sample_pages                  8
    innodb_strict_mode               OFF        
    innodb_support_xa                ON         
    innodb_sync_spin_loops                    30
    innodb_table_locks               ON         
    innodb_thread_concurrency                  0
    innodb_thread_sleep_delay              10000
    innodb_use_native_aio            OFF        
    innodb_use_sys_malloc            ON         
    innodb_version                   1.1.8      
    innodb_write_io_threads                    4
    insert_id                                  0
    interactive_timeout                    28800
    join_buffer_size                      262144
    keep_files_on_create             OFF        
    key_buffer_size                    268435456
    key_cache_age_threshold                  300
    key_cache_block_size                    1024
    key_cache_division_limit                 100
    large_files_support              ON         
    large_page_size                            0
    large_pages                      OFF        
    last_insert_id                             0
    lc_messages                      en_US      
    lc_messages_dir                  /usr/share/
    lc_time_names                    en_US      
    license                          GPL        
    local_infile                     ON         
    lock_wait_timeout                   31536000
    locked_in_memory                 OFF        
    log                              OFF        
    log_bin                          OFF        
    log_bin_trust_function_creators  OFF        
    log_error                        
    log_output                       FILE       
    log_queries_not_using_indexes    OFF        
    log_slave_updates                OFF        
    log_slow_queries                 ON         
    log_warnings                               1
    long_query_time                          0.6
    low_priority_updates             OFF        
    lower_case_file_system           OFF        
    lower_case_table_names                     0
    max_allowed_packet                  16777216
    max_binlog_cache_size                    ###
    max_binlog_size                    104857600
    max_binlog_stmt_cache_size               ###
    max_connect_errors                        10
    max_connections                          100
    max_delayed_threads                       20
    max_error_count                           64
    max_heap_table_size                 25165824
    max_insert_delayed_threads                20
    max_join_size                            ###
    max_length_for_sort_data                1024
    max_long_data_size                  16777216
    max_prepared_stmt_count                16382
    max_relay_log_size                         0
    max_seeks_for_key                        ###
    max_sort_length                         1024
    max_sp_recursion_depth                     0
    max_tmp_tables                            32
    max_user_connections                       0
    max_write_lock_count                     ###
    metadata_locks_cache_size               1024
    min_examined_row_limit                     0
    multi_range_count                        256
    myisam_data_pointer_size                   6
    myisam_max_sort_file_size                ###
    myisam_mmap_size                         ###
    myisam_recover_options           BACKUP     
    myisam_repair_threads                      1
    myisam_sort_buffer_size              8388608
    myisam_stats_method              nulls_unequ
    myisam_use_mmap                  OFF        
    net_buffer_length                      16384
    net_read_timeout                          30
    net_retry_count                           10
    net_write_timeout                         60
    new                              OFF        
              Variable_name             Value   
    old                              OFF        
    old_alter_table                  OFF        
    old_passwords                    OFF        
    open_files_limit                        2158
    optimizer_prune_level                      1
    optimizer_search_depth                    62
    optimizer_switch                 index_merge
    performance_schema               OFF        
    performance_schema_events_waits_h      10000
    performance_schema_events_waits_h         10
    performance_schema_max_cond_class         80
    performance_schema_max_cond_insta       1000
    performance_schema_max_file_class         50
    performance_schema_max_file_handl      32768
    performance_schema_max_file_insta      10000
    performance_schema_max_mutex_clas        200
    performance_schema_max_mutex_inst    1000000
    performance_schema_max_rwlock_cla         30
    performance_schema_max_rwlock_ins    1000000
    performance_schema_max_table_hand     100000
    performance_schema_max_table_inst      50000
    performance_schema_max_thread_cla         50
    performance_schema_max_thread_ins       1000
    pid_file                         /var/run/my
    plugin_dir                       /usr/lib/my
    port                                    3306
    preload_buffer_size                    32768
    profiling                        OFF        
    profiling_history_size                    15
    protocol_version                          10
    proxy_user                       
    pseudo_thread_id                      401326
    query_alloc_block_size                  8192
    query_cache_limit                    1048576
    query_cache_min_res_unit                4096
    query_cache_size                    25165824
    query_cache_type                 ON         
    query_cache_wlock_invalidate     OFF        
    query_prealloc_size                     8192
    rand_seed1                                 0
    rand_seed2                                 0
    range_alloc_block_size                  4096
    read_buffer_size                      131072
    read_only                        OFF        
    read_rnd_buffer_size                  262144
    relay_log                        
    relay_log_index                  
    relay_log_info_file              relay-log.i
    relay_log_purge                  ON         
    relay_log_recovery               OFF        
    relay_log_space_limit                      0
    report_host                      
    report_password                  
    report_port                             3306
    report_user                      
    rpl_recovery_rank                          0
    secure_auth                      OFF        
    secure_file_priv                 
    server_id                                  0
    skip_external_locking            ON         
    skip_name_resolve                OFF        
    skip_networking                  OFF        
    skip_show_database               OFF        
    slave_compressed_protocol        OFF        
    slave_exec_mode                  STRICT     
    slave_load_tmpdir                /tmp       
    slave_max_allowed_packet          1073741824
    slave_net_timeout                       3600
    slave_skip_errors                OFF        
    slave_transaction_retries                 10
    slave_type_conversions           
    slow_launch_time                           2
    slow_query_log                   ON         
    slow_query_log_file              /var/log/my
    socket                           /var/run/my
    sort_buffer_size                     2097152
    sql_auto_is_null                 OFF        
    sql_big_selects                  ON         
    sql_big_tables                   OFF        
    sql_buffer_result                OFF        
    sql_log_bin                      ON         
    sql_log_off                      OFF        
    sql_low_priority_updates         OFF        
    sql_max_join_size                        ###
    sql_mode                         
    sql_notes                        ON         
    sql_quote_show_create            ON         
    sql_safe_updates                 OFF        
    sql_select_limit                         ###
    sql_slave_skip_counter                     0
    sql_warnings                     OFF        
    ssl_ca                           
    ssl_capath                       
    ssl_cert                         
    ssl_cipher                       
    ssl_key                          
    storage_engine                   InnoDB     
    stored_program_cache                     256
    sync_binlog                                0
    sync_frm                         ON         
              Variable_name             Value   
    sync_master_info                           0
    sync_relay_log                             0
    sync_relay_log_info                        0
    system_time_zone                 NZDT       
    table_definition_cache                  1024
    table_open_cache                        1024
    thread_cache_size                          8
    thread_concurrency                        10
    thread_handling                  one-thread-
    thread_stack                          196608
    time_format                      %H:%i:%s   
    time_zone                        SYSTEM     
    timed_mutexes                    OFF        
    timestamp                         1361481911
    tmp_table_size                      16777216
    tmpdir                           /tmp       
    transaction_alloc_block_size            8192
    transaction_prealloc_size               4096
    tx_isolation                     REPEATABLE-
    unique_checks                    ON         
    updatable_views_with_limit       YES        
    version                          5.5.29-0ubu
    version_comment                  (Ubuntu)   
    version_compile_machine          x86_64     
    version_compile_os               debian-linu
    wait_timeout                           28800
    warning_count                              0

Como posso melhorar a velocidade dessas consultas?

Uma consulta típica é do estilo

UPDATE `jos_session` 
SET `time`='1361469848',`userid`='0',`usertype`='',`username`='',
    `gid`='0',`guest`='1',`client_id`='0',
    `data`='__default|a:8:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1361469848;s:18:\"session.timer.last\";i:1361469848;s:17:\"session.timer.now\";i:1361469848;s:24:\"session.client.forwarded\";s:28:\"121.205.249.235, 23.15.14.22\";s:22:\"session.client.browser\";s:69:\"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)\";s:8:\"registry\";O:9:\"JRegistry\":3:{s:17:\"_defaultNameSpace\";s:7:\"session\";s:9:\"_registry\";a:2:{s:7:\"session\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}s:11:\"application\";a:1:{s:4:\"data\";O:8:\"stdClass\":1:{s:4:\"lang\";s:5:\"en-GB\";}}}s:7:\"_errors\";a:0:{}}s:4:\"user\";O:5:\"JUser\":19:{s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:3:\"gid\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:3:\"aid\";i:0;s:5:\"guest\";i:1;s:7:\"_params\";O:10:\"JParameter\":7:{s:4:\"_raw\";s:0:\"\";s:4:\"_xml\";N;s:9:\"_elements\";a:0:{}s:12:\"_elementPath\";a:1:{i:0;s:69:\"/var/www/www.seleconlight.com/libraries/joomla/html/parameter/element\";}s:17:\"_defaultNameSpace\";s:8:\"_default\";s:9:\"_registry\";a:1:{s:8:\"_default\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:9:\"_errorMsg\";N;s:7:\"_errors\";a:0:{}}}country_cc|N;' 
WHERE session_id='k3fuukvh8lrfq244u6ic4it605';

Muitas conexões do AWS RDS causam "bloqueio"

`SHOW VARIABLES`

O índice composto funciona para um conjunto maior de dados, mas não menor

otimizar my.ini para consulta de chave

Consulta duplicada de intervalo de otimização do MySQL

Acelere a consulta eficiente para a primeira associação, a última categoria da tabela de associação (mín., máx.)

Atualização para tabela de sessão lenta

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

automatem's questions

SHOW VARIABLES

`SHOW VARIABLES`