Problem with Query that fetched 2,00,000 or more records

[Ravi Bhatt]

Hi all,

I am mysql 5.0.27-standard. The machine on which mysql server is installed has 4GB of RAM. All my mysql tables are innoDB tables. I am facing problems when i run certain query via a mysql function.

I did an explain on the query and the result for explain is as below:

id select_type table type possible_keys key key_len ref rows Extra
1, SIMPLE e index_merge MC_ACCT_NO,EP_ACCT_NO,EP_MEM EP_MEM,EP_ACCT_NO 6,63 null 2144, Using intersect(EP_MEM,EP_ACCT_NO); Using where; Using temporary; Using filesort
1, SIMPLE d ref MC_ACCT_NO,DISP_PHONE MC_ACCT_NO 62 uiiadual.e.MC_ACCT_NO 207, Using where
1, SIMPLE o eq_ref PRIMARY PRIMARY 4 uiiadual.d.DISP_PHONE 1,
1, SIMPLE i eq_ref PRIMARY PRIMARY 4 uiiadual.d.DRV_ADDR_ID 1,
1, SIMPLE r ref DRIVER_ID,RAMP_ID DRIVER_ID 5 uiiadual.d.DRIVER_ID 1,
1, SIMPLE ep eq_ref PRIMARY,EP_ACCT_NO PRIMARY 4 uiiadual.r.RAMP_ID 1,
1, SIMPLE s eq_ref PRIMARY PRIMARY 4 uiiadual.d.DRV_LICENSE_STATE 1,
1, SIMPLE c eq_ref PRIMARY PRIMARY 4 uiiadual.d.DRV_LICENSE_COUNTRY 1,

the query was :

explain SELECT LEFT(e.ep_known_as,4),s.up_code as license_state,d.drv_license_no,d.mc_acct_no,ep.ramp_name,
c.country_name as license_country,d.drv_fname,d.drv_mname,d.drv_lname,i.addr_street1,i.addr_street2,
i.addr_city,i.addr_state,i.addr_country,i.addr_zip,o.idd_ph_no,d.drv_cdl,d.drv_hzrds_matrl_ok,d.drv_double_ok,
d.drv_triple_ok,d.drv_tanker_ok,d.timestamp,IFNULL(d.modified_date,d.created_date) lst_mod_dt,d.drv_status,d.drv_terminated_dt
FROM ep_mc_join_details e,dispatch_office o,driver_details d
LEFT JOIN(idd_address_master i) ON (d.drv_addr_id = i.idd_addr_id )
LEFT JOIN (driver_ramp_list r,ep_ramp_details ep) ON (r.driver_id=d.driver_id AND r.created_by=d.mc_acct_no AND r.created_by=d.mc_acct_no
AND r.ramp_id=ep.ep_rmp_mpg_id AND ep.ep_acct_no='EP065585')
LEFT JOIN (states_master s) ON (d.drv_license_state = s.state_id)
LEFT JOIN (country_master c) ON (d.drv_license_country = c.country_id)
WHERE d.mc_acct_no = e.mc_acct_no AND e.ep_acct_no='EP065585' AND EP_MEM="Y"
AND EP_KNOWN_AS<>"" AND EP_KNOWN_AS NOT LIKE "*%"
AND o.idd_dispoff_id=d.disp_phone AND d.drv_terminated_dt IS NULL
GROUP BY d.mc_acct_no,d.drv_license_no,d.drv_license_state
ORDER BY lst_mod_dt

I have proper indexes on all the tables used in the query.

When this function is called from a batch job, and at the same time i perform some save operation on screen that save operation hangs until above query is complete.

The function fetches records from above query and then inserts/updates records one by one into another table.

Here is my configuration details for mysql (taken from mysql administrator):
In System Variables-> Table Types->InnoDB
------------------------------------------
have_innodb YES
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
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_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_thread_concurrency 8

In System Variables->Memory
----------------------------
max_heap_table_size 16777216
max_tmp_tables 32
thread_stack 196608
tmp_table_size 33554432

In System Variables->Memory->Buffers
------------------------------------
bulk_insert_buffer_size 8388608
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
join_buffer_size 131072
key_buffer_size 8388600
myisam_sort_buffer_size 8388608
net_buffer_length 16384
read_buffer_size 131072
read_rnd_buffer_size 262144
sort_buffer_size 2097144

In System Variables->Memory->Caches
------------------------------------
binlog_cache_size 32768
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
table_cache 64

In General->Miscellaneous
--------------------------
expire_logs_days 0
max_error_count 64
max_length_for_sort_data 1024
preload_buffer_size 32768
relay_log_purge ON
secure_auth OFF
storage_engine MyISAM

In General->Performance
------------------------
expire_logs_days 0
max_error_count 64
max_length_for_sort_data 1024
preload_buffer_size 32768
relay_log_purge ON
secure_auth OFF
storage_engine MyISAM

Hope i have given all the information. Please help me on this. Any help will be greatly appreciated.

Please let me know if you need any other information.

Thanks in advance.