Eko, you're not JOINing the two tables together - your database is
taking the time to produce a huge result set known as a cartesian
product, which is probably not what you want. You need to introduce an
additional WHERE clause specifying how the two tables should be JOINed, like
WHERE hiart01a.column = hiarf01.column
AND hiart01a.tgl>=from_days(to_days(now()) - 180) and
hiart01a.tgl<=from_days(to_days(now()) - 30)
Also, if you do not have indexes on the columns the two tables JOIN on,
that would help, as would an index on hiart01a.tgl - since you are
searching on that column.
Posting table structures when you have a question like this is always
helpful - the output from 'SHOW CREATE TABLE hiart01a;' for example.
Good luck,
Dan
Eko Budiharto wrote:
I have a query statement like this
select hiart01a.cust, hiarf01.nama, hiarf01.al2, hiart01a.tgl, hiart01a.netto, hiart01a.muka
from hiart01a, hiarf01
where hiart01a.tgl>=from_days(to_days(now()) - 180) and
hiart01a.tgl<=from_days(to_days(now()) - 30)
order by hiart01a.cust;
when I run this statement, it takes more than 2 hours.
Detail information:
-. there are 200K rows in both tables (hiart01a, hiarf01)
-. I am using mysql 5.019
-. I am running on testing server (pentium 4, 3GHz, RAM 1GB)
and the system variables like this
innodb_additional_mem_pool_size 25165824 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_additional_mem_pool_size 25165824 innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0 &
nbsp;
innodb_buffer_pool_size 734003200
innodb_log_buffer_size 4194304
innodb_log_file_size 17825792 innodb_open_files 300 innodb_thread_concurrency 100
max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors 10
max_connections 800
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 4294967295 max_length_for_sort_data 1024
max_relay_log_size 0
max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables 32
max_user_connections 0
query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
read_buffer_size 61440
I am wondering there is a way to make the query process faster.
I am looking forward to a favorable reply from you. Thank you.
Regards,
Eko
---------------------------------
Yahoo! Sports Fantasy Football ’06 - Go with the leader. Start your league today!
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
