MySQL Forums
Forum List  »  Memory Storage Engine

Problem with Query that fetched 2,00,000 or more records
Posted by: Ravi Bhatt
Date: February 21, 2008 03:25AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with Query that fetched 2,00,000 or more records
6522
February 21, 2008 03:25AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.