MySQL Forums :: Newbie :: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL


Advanced Search

Killed by joins. 1 minute selects in Access, 15 minutes in mySQL
Posted by: Mark Daly ()
Date: July 10, 2011 12:41AM

Hi everyone,

I know enough about mySQL to recogize that my database is suffering performance issues, but not enough to know how to fix it. I'm hoping someone smarter than me can offer some helpful suggestions. After hours of tweaking, this one has me beaten.

In short, I'm getting killed on joins. For example, two tables I'm attempting to join have 6000 and 9,000,000 rows respectively. The bigger fact table is partitioned on year and month (i.e. currently 12 months).

Running simple join queries in Access gets results in about 1 minute. It takes 15 min on average in mySQL. If I just run a query on the larger table (without joins), the time drops to 30 seconds.

Any help is really, really appreciated. If not for me, do it for mySQL's reputation in my dept :) I'm also going to post in the Performance section.

If there are some details I've omitted that might help, I'm happy to post them.


Simplified Tables:
--------------------
CREATE TABLE `dimension_symbol` (
`SymbolID` smallint(6) NOT NULL AUTO_INCREMENT,
`Symbol` varchar(8) DEFAULT NULL,
KEY `Index 1` (`SymbolID`)
) ENGINE=InnoDB AUTO_INCREMENT=5885 DEFAULT CHARSET=latin1 ;

CREATE TABLE `fact_amounts` (
`Exec_Date` date DEFAULT NULL,
`SymbolID` smallint(6) DEFAULT NULL,
`Tier` varchar(5) DEFAULT NULL,
`Amount` double DEFAULT NULL,
KEY `Index 1` (`Exec_Date`),
KEY `Index 2` (`SymbolID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT ;


Explain and Results:
----------------------
explain extended
select b.symbol, a.tier, sum(a.amount)
from dimension_symbol as b, fact_amounts as a
where b.symbolid= a.symbolid
group by b.symbol, a.tier;


id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, b, index, Index 1, Index 1, 13, NULL, 5704, 100.00, Using index; Using temporary; Using filesort
1, SIMPLE, a, ref, Index 2, Index 2, 3, trades.b.SymbolID, 469, 100.00, Using where


Sample Inserts
-----------------------
You really need volume for this, but here are a few sample inserts. It's only a few seconds work to create more once you have these.

insert into dimension_symbol (SymbolID, Symbol) Values ("1","AA");
insert into dimension_symbol (SymbolID, Symbol) Values ("2","AAA");
insert into dimension_symbol (SymbolID, Symbol) Values ("3","AAB");

insert into fact_amounts (Exec_Date, SymbolID, Amount) Values ("2012-02-02",1, "Tier A", 100);
insert into fact_amounts (Exec_Date, SymbolID, Amount) Values ("2012-02-03",1, "Tier B", 350);
insert into fact_amounts (Exec_Date, SymbolID, Amount) Values ("2012-02-03",2, "Tier A", 500);


Environment/Settings:
---------------------
- Win XP
- 32 bit (I know, I know. IT refuses to upgrade unless I show up with a crack team of Navy Seals. Consider this a constant)
- RAM limited to 2 GB (see previous note on 32 bit restriction)
- under 5 users
- DB is not transactional. Used for one-off research queries.
- Select version(): 5.5.12

So while I'm sure we all agree the hardware is sub-optimal, that doesn't explain why I can run the same query in Access in 1/15th the time. This is a user problem, not a hardware problem.

I've played with various buffer configurations for the 2 GB I have available, and the best seems to be:

tmp_table_size = 250M
myisam_sort_buffer_size = 64M
innodb_buffer_pool_size = 1100M
innodb_log_file_size = 450M


show variables like 'inno%';

Variable_name:Value
innodb_adaptive_flushing:ON
innodb_adaptive_hash_index:ON
innodb_additional_mem_pool_size:2097152
innodb_autoextend_increment:8
innodb_autoinc_lock_mode:1
innodb_buffer_pool_instances:1
innodb_buffer_pool_size:1363148800
innodb_change_buffering:all
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_format:Antelope
innodb_file_format_check:ON
innodb_file_format_max:Antelope
innodb_file_per_table:ON
innodb_flush_log_at_trx_commit:1
innodb_flush_method:
innodb_force_recovery:0
innodb_io_capacity:200
innodb_lock_wait_timeout:50
innodb_locks_unsafe_for_binlog:OFF
innodb_log_buffer_size:10485760
innodb_log_file_size:471859200
innodb_log_files_in_group:2
innodb_log_group_home_dir:.\
innodb_max_dirty_pages_pct:75
innodb_max_purge_lag:0
innodb_mirrored_log_groups:1
innodb_old_blocks_pct:37
innodb_old_blocks_time:0
innodb_open_files:300
innodb_purge_batch_size:20
innodb_purge_threads:0
innodb_read_ahead_threshold:56
innodb_read_io_threads:4
innodb_replication_delay:0
innodb_rollback_on_timeout:OFF
innodb_rollback_segments:128
innodb_spin_wait_delay:6
innodb_stats_method:nulls_equal
innodb_stats_on_metadata:ON
innodb_stats_sample_pages:8
innodb_strict_mode:OFF
innodb_support_xa:ON
innodb_sync_spin_loops:30
innodb_table_locks:ON
innodb_thread_concurrency:10
innodb_thread_sleep_delay:10000
innodb_use_native_aio:ON
innodb_use_sys_malloc:ON
innodb_version:1.1.6
innodb_write_io_threads:4


show status like 'inno%';

Variable_name:Value
Innodb_buffer_pool_pages_data:76703
Innodb_buffer_pool_pages_dirty:0
Innodb_buffer_pool_pages_flushed:220
Innodb_buffer_pool_pages_free:1
Innodb_buffer_pool_pages_misc:6496
Innodb_buffer_pool_pages_total:83200
Innodb_buffer_pool_read_ahead:63
Innodb_buffer_pool_read_ahead_evicted:955
Innodb_buffer_pool_read_requests:82326814
Innodb_buffer_pool_reads:227665
Innodb_buffer_pool_wait_free:0
Innodb_buffer_pool_write_requests:12430
Innodb_data_fsyncs:87
Innodb_data_pending_fsyncs:0
Innodb_data_pending_reads:0
Innodb_data_pending_writes:0
Innodb_data_read:3733278720
Innodb_data_reads:227799
Innodb_data_writes:191
Innodb_data_written:4077568
Innodb_dblwr_pages_written:110
Innodb_dblwr_writes:12
Innodb_have_atomic_builtins:ON
Innodb_log_waits:0
Innodb_log_write_requests:1033
Innodb_log_writes:20
Innodb_os_log_fsyncs:31
Innodb_os_log_pending_fsyncs:0
Innodb_os_log_pending_writes:0
Innodb_os_log_written:467456
Innodb_page_size:16384
Innodb_pages_created:38
Innodb_pages_read:227727
Innodb_pages_written:110
Innodb_row_lock_current_waits:0
Innodb_row_lock_time:0
Innodb_row_lock_time_avg:0
Innodb_row_lock_time_max:0
Innodb_row_lock_waits:0
Innodb_rows_deleted:0
Innodb_rows_inserted:1948
Innodb_rows_read:26404395
Innodb_rows_updated:0
Innodb_truncated_status_writes:0

Options: ReplyQuote


Subject Written By Posted
Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/10/2011 12:41AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Peter Brawley 07/10/2011 06:16AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/11/2011 12:20AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Peter Brawley 07/11/2011 06:40AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Rick James 07/11/2011 11:14AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/14/2011 10:32AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/17/2011 12:47PM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/17/2011 12:49PM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Rick James 07/17/2011 01:15PM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/17/2011 08:53PM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Rick James 07/17/2011 09:41PM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/18/2011 12:09AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Rick James 07/18/2011 09:22AM
Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL Mark Daly 07/19/2011 09:43AM


Sorry, you can't reply to this topic. It has been closed.

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.