title: Mysql query staying in 'SENDING DATA' state for long time when using LEFT JOIN
body: <p>I have query which is going in the <strong>SENDING DATA</strong> state for very long period of time.
  Can someone please help me with this : below are details </p>
  
  <p><strong>Mysql Query:</strong></p>
  
  <pre><code>select a.msgId,a.senderId,a.destination,a.inTime,a.status as InStatus,b.status as SubStatus,c.deliverTime,substr(c.receipt,82,7) as DlvStatus 
  from inserted_history a  left join submitted_history b  on b.msgId = a.msgId left join delivered_history c  on a.msgId = c.msgId 
  where a.inTime between '2010-08-10 00:00:00' and '2010-08-010 23:59:59' and a.systemId='ND_arber'
  </code></pre>
  
  <p>Total records in <code>delivered_history</code> : <strong>223870168</strong></p>
  
  <p>Total records in <code>inserted_history</code> : <strong>264817239</strong></p>
  
  <p>Total records in <code>submitted_history</code> : <strong>226637058</strong></p>
  
  <p><strong>Explain</strong> query returns:</p>
  
  <pre><code>id  ,  select_type  ,  table  ,  type  ,  possible_keys  ,  key  ,  key_len  ,  ref  ,  rows  ,  Extra  
    1  ,  SIMPLE  ,  a  ,  ref  ,  systemId,idx_time  ,  systemId  ,  14  ,  const  ,  735310  ,  Using where  
    1  ,  SIMPLE  ,  b  ,  ref  ,  PRIMARY  ,  PRIMARY  ,  66  ,  gwreports2.a.msgId  ,  2270405  ,    
    1  ,  SIMPLE  ,  c  ,  ref  ,  PRIMARY  ,  PRIMARY  ,  66  ,  gwreports2.a.msgId  ,  2238701  ,  
  </code></pre>
  
  <p><strong>CREATE TABLE for delivered_history</strong></p>
  
  <pre><code>CREATE TABLE `delivered_history` (
   `msgId` VARCHAR(64) NOT NULL,
   `systemId` VARCHAR(12) NOT NULL,
   `deliverTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
   `smscId` VARCHAR(64) NOT NULL,
   `smsc` VARCHAR(20) NOT NULL,
   `receipt` BLOB NULL,
   `errcode` INT(11) NULL DEFAULT NULL,
   PRIMARY KEY (`msgId`, `deliverTime`),
   INDEX `systemId` (`systemId`),
   INDEX `smsc` (`smsc`),
   INDEX `idx_time` (`deliverTime`)
  )
  ROW_FORMAT=DEFAULT
  </code></pre>
  
  <p><strong>CREATE TABLE for inserted_history</strong></p>
  
  <pre><code>CREATE TABLE `inserted_history` (
   `msgId` VARCHAR(64) NOT NULL,
   `systemId` VARCHAR(12) NOT NULL,
   `senderId` VARCHAR(15) NOT NULL,
   `destination` VARCHAR(15) NOT NULL,
   `inTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
   `status` VARCHAR(20) NOT NULL,
   `msgText` BLOB NULL,
   `msgType` VARCHAR(15) NULL DEFAULT NULL,
   PRIMARY KEY (`msgId`, `inTime`),
   INDEX `systemId` (`systemId`),
   INDEX `senderId` (`senderId`),
   INDEX `destination` (`destination`),
   INDEX `status` (`status`),
   INDEX `idx_time` (`inTime`)
  )
  ROW_FORMAT=DEFAULT
  </code></pre>
  
  <p><strong>CREATE TABLE for submitted_history</strong></p>
  
  <pre><code>CREATE TABLE `submitted_history` (
   `msgId` VARCHAR(64) NOT NULL,
   `systemId` VARCHAR(12) NOT NULL,
   `submitTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
   `status` VARCHAR(20) NOT NULL,
   `smscId` VARCHAR(64) NOT NULL,
   `smsc` VARCHAR(16) NOT NULL,
   `errcode` INT(6) NULL DEFAULT '0',
   PRIMARY KEY (`msgId`, `submitTime`),
   INDEX `systemId` (`systemId`),
   INDEX `smsc` (`smsc`),
   INDEX `status` (`status`),
   INDEX `idx_time` (`submitTime`)
  )
  ROW_FORMAT=DEFAULT
  </code></pre>
  
  <p><strong>ALL TABLES ARE DATE PARTIONED</strong> on the timestamp fields</p>
  
  <p>List of the global variables in <code>Mysql Server</code> </p>
  
  <pre><code>Variable_name  ,  Value  
    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_format  ,  STATEMENT  
    bulk_insert_buffer_size  ,  8388608  
    character_set_client  ,  latin1  
    character_set_connection  ,  latin1  
    character_set_database  ,  latin1  
    character_set_filesystem  ,  binary  
    character_set_results  ,  latin1  
    character_set_server  ,  latin1  
    character_set_system  ,  utf8  
    character_sets_dir  ,  /usr/share/mysql/charsets/  
    collation_connection  ,  latin1_swedish_ci  
    collation_database  ,  latin1_swedish_ci  
    collation_server  ,  latin1_swedish_ci  
    completion_type  ,  0  
    concurrent_insert  ,  1  
    connect_timeout  ,  10  
    datadir  ,  /var/lib/mysql/  
    date_format  ,  %Y-%m-%d  
    datetime_format  ,  %Y-%m-%d %H:%i:%s  
    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  
    flush  ,  OFF  
    flush_time  ,  0  
    foreign_key_checks  ,  ON  
    ft_boolean_syntax  ,  + -&gt;&lt;()~*:        &amp;|  
    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/run/mysqld/mysqld.log  
    group_concat_max_len  ,  1024  
    have_community_features  ,  YES  
    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_query_cache  ,  YES  
    have_rtree_keys  ,  YES  
    have_ssl  ,  DISABLED  
    have_symlink  ,  YES  
    hostname  ,  smscdb  
    identity  ,  0  
    ignore_builtin_innodb  ,  OFF  
    init_connect  ,    
    init_file  ,    
    init_slave  ,    
    innodb_adaptive_hash_index  ,  ON  
    innodb_additional_mem_pool_size  ,  1048576  
    innodb_autoextend_increment  ,  8  
    innodb_autoinc_lock_mode  ,  1  
    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_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_rollback_on_timeout  ,  OFF  
    innodb_stats_on_metadata  ,  ON  
    innodb_support_xa  ,  ON  
    innodb_sync_spin_loops  ,  20  
    innodb_table_locks  ,  ON  
    innodb_thread_concurrency  ,  8  
    innodb_thread_sleep_delay  ,  10000  
    innodb_use_legacy_cardinality_algorithm  ,  ON  
    insert_id  ,  0  
    interactive_timeout  ,  28800  
    join_buffer_size  ,  131072  
    keep_files_on_create  ,  OFF  
    key_buffer_size  ,  1073741824  
    key_cache_age_threshold  ,  300  
    key_cache_block_size  ,  1024  
    key_cache_division_limit  ,  100  
    language  ,  /usr/share/mysql/english/  
    large_files_support  ,  ON  
    large_page_size  ,  0  
    large_pages  ,  OFF  
    last_insert_id  ,  0  
    lc_time_names  ,  en_US  
    license  ,  GPL  
    local_infile  ,  ON  
    locked_in_memory  ,  OFF  
    log  ,  OFF  
    log_bin  ,  ON  
    log_bin_trust_function_creators  ,  OFF  
    log_bin_trust_routine_creators  ,  OFF  
    log_error  ,    
    log_output  ,  FILE  
    log_queries_not_using_indexes  ,  OFF  
    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  ,  536870912  
    max_binlog_cache_size  ,  4294963200  
    max_binlog_size  ,  104857600  
    max_connect_errors  ,  10  
    max_connections  ,  151  
    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_prepared_stmt_count  ,  16382  
    max_relay_log_size  ,  0  
    max_seeks_for_key  ,  4294967295  
    max_sort_length  ,  1024  
    max_sp_recursion_depth  ,  0  
    max_tmp_tables  ,  32  
    max_user_connections  ,  0  
    max_write_lock_count  ,  4294967295  
    min_examined_row_limit  ,  0  
    multi_range_count  ,  256  
    myisam_data_pointer_size  ,  6  
    myisam_max_sort_file_size  ,  2146435072  
    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  
    new  ,  OFF  
    old  ,  OFF  
    old_alter_table  ,  OFF  
    old_passwords  ,  OFF  
    open_files_limit  ,  20000  
    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  
    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  
    pseudo_thread_id  ,  0  
    query_alloc_block_size  ,  8192  
    query_cache_limit  ,  1073741824  
    query_cache_min_res_unit  ,  4096  
    query_cache_size  ,  536870912  
    query_cache_type  ,  ON  
    query_cache_wlock_invalidate  ,  OFF  
    query_prealloc_size  ,  8192  
    rand_seed1  ,    
    rand_seed2  ,    
    range_alloc_block_size  ,  4096  
    read_buffer_size  ,  131072  
    read_only  ,  OFF  
    read_rnd_buffer_size  ,  33554432  
    relay_log  ,    
    relay_log_index  ,    
    relay_log_info_file  ,  relay-log.info  
    relay_log_purge  ,  ON  
    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  ,  3  
    skip_external_locking  ,  ON  
    skip_networking  ,  OFF  
    skip_show_database  ,  OFF  
    slave_compressed_protocol  ,  OFF  
    slave_exec_mode  ,  STRICT  
    slave_load_tmpdir  ,  /tmp  
    slave_net_timeout  ,  3600  
    slave_skip_errors  ,  OFF  
    slave_transaction_retries  ,  10  
    slow_launch_time  ,  2  
    slow_query_log  ,  OFF  
    slow_query_log_file  ,  /var/run/mysqld/mysqld-slow.log  
    socket  ,  /var/run/mysqld/mysqld.sock  
    sort_buffer_size  ,  67108864  
    sql_auto_is_null  ,  ON  
    sql_big_selects  ,  ON  
    sql_big_tables  ,  OFF  
    sql_buffer_result  ,  OFF  
    sql_log_bin  ,  ON  
    sql_log_off  ,  OFF  
    sql_log_update  ,  ON  
    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  ,    
    sql_warnings  ,  OFF  
    ssl_ca  ,    
    ssl_capath  ,    
    ssl_cert  ,    
    ssl_cipher  ,    
    ssl_key  ,    
    storage_engine  ,  MyISAM  
    sync_binlog  ,  0  
    sync_frm  ,  ON  
    system_time_zone  ,  IST  
    table_definition_cache  ,  256  
    table_lock_wait_timeout  ,  50  
    table_open_cache  ,  500  
    table_type  ,  MyISAM  
    thread_cache_size  ,  8  
    thread_handling  ,  one-thread-per-connection  
    thread_stack  ,  196608  
    time_format  ,  %H:%i:%s  
    time_zone  ,  SYSTEM  
    timed_mutexes  ,  OFF  
    timestamp  ,  1282125419  
    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.1.37-1ubuntu5-log  
    version_comment  ,  (Ubuntu)  
    version_compile_machine  ,  i486  
    version_compile_os  ,  debian-linux-gnu  
    wait_timeout  ,  28800  
    warning_count  ,  0  
  </code></pre>