MySQL query performance issue with MyISAM table -

folks

i have table( billing_data) 60 million records. table engine myisam. have stored procedure read csv file , dump in temp table every 10-15 mins , inserted in table( billing_data). there lot of records being inserted in table.

we trying run query below on same table( plus other tables in join) generate report.

"select b.destination,b.release_cause_protocol_stack,b.binary_value_protocol_stack,b.release_cause billing_datas b inner join (select id carriermasters id in (99, 100, 101) ) c1 on b.carrierid_customer=c1.idinner join (select id technicalprofiles id in (83, 274, 84, 416)) t1 on b.technical_profileid_customer=t1.id  inner join (select trunk trunks trunk in (90409, 90310, 30230, 30313) ) tr1 on b.origination_trunkid=tr1.trunk inner join (select id carriermasters id in (214, 215, 59, 60, 62, 292, 63, 216, 64, 61, 217, 274, 58) ) c2 on b.carrierid_supplier=c2.id  inner join (select id technicalprofiles id in (223, 55, 224, 56, 225, 57, 226, 58, 227, 228, 229, 230, 393, 394, 395, 59, 231, 232, 233, 460, 234)) t2 on b.technical_profileid_supplier=t2.id inner join (select trunk trunks trunk in (90106, 30249, 90249, 30250, 90250, 90445, 30409, 90446, 30410, 30294, 90363, 30293, 90293, 30175, 30146, 90146, 90435, 90049, 30177, 90294, 90130, 30130, 90189, 30145, 90145, 90389, 30400, 90429, 90121, 90159)) tr2 on b.supplierid=tr2.trunk b.start_time_date between '2013-08-02 00:00:00' , '2013-08-02 23:59:59' order b.start_time_date asc" 

now scenario

a) if stop cron job insert data in billing_datas table, above query takes more 40 mins.

b) if cron job running ( inserting data file) temp table , billing_data. @ time above query run forever.

i m not sure m getting wrong.

we checked indexes , fine.

mysql query : show variables

---

automatic_sp_privileges on back_log    50 big_tables  off  binlog_cache_size   32768 binlog_direct_non_transactional_updates off binlog_format   statement bulk_insert_buffer_size 8388608 completion_type 0 concurrent_insert   1 connect_timeout 10 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 event_scheduler off flush   off flush_time  0 foreign_key_checks  on ft_query_expansion_limit    20 ignore_builtin_innodb   off 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_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_method nulls_equal 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 8384512 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 local_infile    on locked_in_memory    off log off log_bin off log_bin_trust_function_creators off log_bin_trust_routine_creators  off long_query_time 10 low_priority_updates    off max_allowed_packet  1048576 max_binlog_cache_size   1.84467e+19 max_binlog_size 1073741824 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   1.84467e+19 max_length_for_sort_data    1024 max_long_data_size  1048576 max_prepared_stmt_count 16382 max_relay_log_size  0 max_seeks_for_key   1.84467e+19 max_sort_length 1024 max_sp_recursion_depth  0 max_tmp_tables  32 max_user_connections    0 max_write_lock_count    1.84467e+19 min_examined_row_limit  0 multi_range_count   256 myisam_data_pointer_size    6 myisam_max_sort_file_size   9.22337e+18 myisam_mmap_size    1.84467e+19 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 open_files_limit    1024 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 preload_buffer_size 32768 pseudo_thread_id    7256 query_alloc_block_size  8192 query_cache_limit   1048576 query_cache_min_res_unit    4096 query_cache_size    0 query_cache_type    on query_cache_wlock_invalidate    off query_prealloc_size 8192 range_alloc_block_size  4096 read_buffer_size    131072 read_only   off read_rnd_buffer_size    262144 skip_external_locking   on slow_launch_time    2 sort_buffer_size    2097144 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   1.84467e+19 sql_quote_show_create   on sql_safe_updates    off sql_select_limit    1.84467e+19 storage_engine  myisam sync_binlog 0 sync_frm    on table_definition_cache  256 table_lock_wait_timeout 50 table_open_cache    64 table_type  myisam thread_cache_size   0 thread_handling one-thread-per-connection thread_stack    262144 timed_mutexes   off tmp_table_size  16777216 transaction_alloc_block_size    8192 transaction_prealloc_size   4096 unique_checks   on updatable_views_with_limit  yes version 05/01/69 version_compile_os  redhat-linux-gnu wait_timeout    28800 

select b.destination      , b.release_cause_protocol_stack      , b.binary_value_protocol_stack      , b.release_cause    billing_datas b    join carriermasters customer     on b.carrierid_customer = customer.id   join technicalprofiles tpc      on b.technical_profileid_customer = tpc.id     join trunks kc      on b.origination_trunkid = kc.trunk    join carriermasters supplier      on b.carrierid_supplier = supplier.id     join technicalprofiles tps     on b.technical_profileid_supplier = tps.id    join trunks ks      on b.supplierid = ks.trunk   b.start_time_date between '2013-08-02 00:00:00' , '2013-08-02 23:59:59'     , customer.id in (99, 100, 101)     , tpc.id in (83, 274, 84, 416)    , kc.trunk in (90409, 90310, 30230, 30313)     , supplier.id in (214, 215, 59, 60, 62, 292, 63, 216, 64, 61, 217, 274, 58)     , tps.id in (223, 55, 224, 56, 225, 57, 226, 58, 227, 228, 229, 230, 393, 394, 395, 59, 231, 232, 233, 460, 234)    , ks.trunk in (90106, 30249, 90249, 30250, 90250, 90445, 30409, 90446, 30410, 30294, 90363, 30293, 90293, 30175, 30146, 90146, 90435, 90049, 30177, 90294, 90130, 30130, 90189, 30145, 90145, 90389, 30400, 90429, 90121, 90159)  order       b.start_time_date asc;