asked on

MySQL Server 5.5.36 Tuning

I recently inherited a MySQL dbase that is hosting data for an Access Front end using ODBC. In essence the Schema consists of about 8 large tables (6 Million rows +) and an number of secondary tables. The data is stored thus:
Let's say I had a record that held name, City, State, Zip
One would expect a single row with each piece in a column formatted to hold that data, indexed with a unique primary key.

Using that same model, this database assigns an index to the type of data being stored (1=name, 2=City,3=address,4=state) and an index for the root context of the dataset (let's say its the name)
Then stores each piece of data on its own row, referencing the data type index and the root index. Each row has a column to hold different data types (Double, Date/Time, Varchar, etc)
So in order to retrieve the complete record, a query must join the table to itself 4 times on the root context id and then pull each column.

With this schema, anything one does is going to be slow, of course. I have been tasked with tuning the database as best as possible to address the ever increasing lag time on queries.

I have plenty of resources available. CPU usages stays below 25% and memory below 40%. Attcahed is the my.cnf.
I don't even know where to start on this. Discuss and thank you!

JP_TechGroup

ASKER

Below is the current status:
GLOBAL STATUS:
Aborted_clients....................................... 3
Aborted_connects...................................... 0
Binlog_cache_disk_use................................. 0
Binlog_cache_use...................................... 0
Binlog_stmt_cache_disk_use............................ 0
Binlog_stmt_cache_use................................. 0
Bytes_received........................................ 603007409
Bytes_sent............................................ 24151244728
Com_admin_commands.................................... 1
Com_assign_to_keycache................................ 0
Com_alter_db.......................................... 0
Com_alter_db_upgrade.................................. 0
Com_alter_event....................................... 0
Com_alter_function.................................... 0
Com_alter_procedure................................... 0
Com_alter_server...................................... 0
Com_alter_table....................................... 0
Com_alter_tablespace.................................. 0
Com_analyze........................................... 0
Com_begin............................................. 0
Com_binlog............................................ 0
Com_call_procedure.................................... 0
Com_change_db......................................... 38
Com_change_master..................................... 0
Com_check............................................. 0
Com_checksum.......................................... 0
Com_commit............................................ 14924
Com_create_db......................................... 0
Com_create_event...................................... 0
Com_create_function................................... 0
Com_create_index...................................... 0
Com_create_procedure.................................. 0
Com_create_server..................................... 0
Com_create_table...................................... 0
Com_create_trigger.................................... 0
Com_create_udf........................................ 0
Com_create_user....................................... 0
Com_create_view....................................... 0
Com_dealloc_sql....................................... 0
Com_delete............................................ 12
Com_delete_multi...................................... 0
Com_do................................................ 0
Com_drop_db........................................... 0
Com_drop_event........................................ 0
Com_drop_function..................................... 0
Com_drop_index........................................ 0
Com_drop_procedure.................................... 0
Com_drop_server....................................... 0
Com_drop_table........................................ 0
Com_drop_trigger...................................... 0
Com_drop_user......................................... 0
Com_drop_view......................................... 0
Com_empty_query....................................... 0
Com_execute_sql....................................... 0
Com_flush............................................. 0
Com_grant............................................. 0
Com_ha_close.......................................... 0
Com_ha_open........................................... 0
Com_ha_read........................................... 0
Com_help.............................................. 0
Com_insert............................................ 12369
Com_insert_select..................................... 1
Com_install_plugin.................................... 0
Com_kill.............................................. 0
Com_load.............................................. 0
Com_lock_tables....................................... 0
Com_optimize.......................................... 0
Com_preload_keys...................................... 0
Com_prepare_sql....................................... 0
Com_purge............................................. 0
Com_purge_before_date................................. 0
Com_release_savepoint................................. 0
Com_rename_table...................................... 0
Com_rename_user....................................... 0
Com_repair............................................ 0
Com_replace........................................... 0
Com_replace_select.................................... 0
Com_reset............................................. 0
Com_resignal.......................................... 0
Com_revoke............................................ 0
Com_revoke_all........................................ 0
Com_rollback.......................................... 3
Com_rollback_to_savepoint............................. 0
Com_savepoint......................................... 0
Com_select............................................ 1216214
Com_set_option........................................ 30145
Com_signal............................................ 0
Com_show_authors...................................... 0
Com_show_binlog_events................................ 0
Com_show_binlogs...................................... 0
Com_show_charsets..................................... 0
Com_show_collations................................... 0
Com_show_contributors................................. 0
Com_show_create_db.................................... 1
Com_show_create_event................................. 0
Com_show_create_func.................................. 0
Com_show_create_proc.................................. 0
Com_show_create_table................................. 1
Com_show_create_trigger............................... 0
Com_show_databases.................................... 2
Com_show_engine_logs.................................. 0
Com_show_engine_mutex................................. 0
Com_show_engine_status................................ 0
Com_show_events....................................... 1
Com_show_errors....................................... 0
Com_show_fields....................................... 88
Com_show_function_status.............................. 1
Com_show_grants....................................... 0
Com_show_keys......................................... 78
Com_show_master_status................................ 0
Com_show_open_tables.................................. 0
Com_show_plugins...................................... 5
Com_show_privileges................................... 0
Com_show_procedure_status............................. 1
Com_show_processlist.................................. 772
Com_show_profile...................................... 0
Com_show_profiles..................................... 0
Com_show_relaylog_events.............................. 0
Com_show_slave_hosts.................................. 0
Com_show_slave_status................................. 0
Com_show_status....................................... 2310
Com_show_storage_engines.............................. 1
Com_show_table_status................................. 18
Com_show_tables....................................... 1
Com_show_triggers..................................... 3
Com_show_variables.................................... 64
Com_show_warnings..................................... 0
Com_slave_start....................................... 0
Com_slave_stop........................................ 0
Com_stmt_close........................................ 0
Com_stmt_execute...................................... 0
Com_stmt_fetch........................................ 0
Com_stmt_prepare...................................... 0
Com_stmt_reprepare.................................... 0
Com_stmt_reset........................................ 0
Com_stmt_send_long_data............................... 0
Com_truncate.......................................... 0
Com_uninstall_plugin.................................. 0
Com_unlock_tables..................................... 0
Com_update............................................ 7864
Com_update_multi...................................... 0
Com_xa_commit......................................... 0
Com_xa_end............................................ 0
Com_xa_prepare........................................ 0
Com_xa_recover........................................ 0
Com_xa_rollback....................................... 0
Com_xa_start.......................................... 0
Compression........................................... OFF
Connections........................................... 112
Created_tmp_disk_tables............................... 333
Created_tmp_files..................................... 602
Created_tmp_tables.................................... 3812
Delayed_errors........................................ 0
Delayed_insert_threads................................ 0
Delayed_writes........................................ 0
Flush_commands........................................ 1
Handler_commit........................................ 1248237
Handler_delete........................................ 12
Handler_discover...................................... 0
Handler_prepare....................................... 0
Handler_read_first.................................... 10430
Handler_read_key...................................... 147624609
Handler_read_last..................................... 0
Handler_read_next..................................... 413629036
Handler_read_prev..................................... 0
Handler_read_rnd...................................... 12683577
Handler_read_rnd_next................................. 1143047276
Handler_rollback...................................... 598
Handler_savepoint..................................... 0
Handler_savepoint_rollback............................ 0
Handler_update........................................ 165561
Handler_write......................................... 20946908
Innodb_buffer_pool_pages_data......................... 202506
Innodb_buffer_pool_bytes_data......................... 3317858304
Innodb_buffer_pool_pages_dirty........................ 21
Innodb_buffer_pool_bytes_dirty........................ 344064
Innodb_buffer_pool_pages_flushed...................... 44653
Innodb_buffer_pool_pages_free......................... 229840
Innodb_buffer_pool_pages_misc......................... 26406
Innodb_buffer_pool_pages_total........................ 458752
Innodb_buffer_pool_read_ahead_rnd..................... 0
Innodb_buffer_pool_read_ahead......................... 8401
Innodb_buffer_pool_read_ahead_evicted................. 0
Innodb_buffer_pool_read_requests...................... 897543819
Innodb_buffer_pool_reads.............................. 192398
Innodb_buffer_pool_wait_free.......................... 0
Innodb_buffer_pool_write_requests..................... 193389
Innodb_data_fsyncs.................................... 25580
Innodb_data_pending_fsyncs............................ 0
Innodb_data_pending_reads............................. 0
Innodb_data_pending_writes............................ 0
Innodb_data_read...................................... 3313111040
Innodb_data_reads..................................... 202093
Innodb_data_writes.................................... 69104
Innodb_data_written................................... 1485274624
Innodb_dblwr_pages_written............................ 44653
Innodb_dblwr_writes................................... 1446
Innodb_have_atomic_builtins........................... ON
Innodb_log_waits...................................... 0
Innodb_log_write_requests............................. 23771
Innodb_log_writes..................................... 21798
Innodb_os_log_fsyncs.................................. 22697
Innodb_os_log_pending_fsyncs.......................... 0
Innodb_os_log_pending_writes.......................... 0
Innodb_os_log_written................................. 21624320
Innodb_page_size...................................... 16384
Innodb_pages_created.................................. 424
Innodb_pages_read..................................... 202082
Innodb_pages_written.................................. 44653
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................................... 12
Innodb_rows_inserted.................................. 11775
Innodb_rows_read...................................... 1573941789
Innodb_rows_updated................................... 7799
Innodb_truncated_status_writes........................ 0
Key_blocks_not_flushed................................ 0
Key_blocks_unused..................................... 263453
Key_blocks_used....................................... 2200
Key_read_requests..................................... 74160895
Key_reads............................................. 4
Key_write_requests.................................... 14041876
Key_writes............................................ 0
Last_query_cost....................................... 0.000000
Max_used_connections.................................. 33
Not_flushed_delayed_rows.............................. 0
Open_files............................................ 8
Open_streams.......................................... 0
Open_table_definitions................................ 330
Open_tables........................................... 105
Opened_files.......................................... 2492
Opened_table_definitions.............................. 308
Opened_tables......................................... 981
Performance_schema_cond_classes_lost.................. 0
Performance_schema_cond_instances_lost................ 0
Performance_schema_file_classes_lost.................. 0
Performance_schema_file_handles_lost.................. 0
Performance_schema_file_instances_lost................ 0
Performance_schema_locker_lost........................ 0
Performance_schema_mutex_classes_lost................. 0
Performance_schema_mutex_instances_lost............... 0
Performance_schema_rwlock_classes_lost................ 0
Performance_schema_rwlock_instances_lost.............. 0
Performance_schema_table_handles_lost................. 0
Performance_schema_table_instances_lost............... 0
Performance_schema_thread_classes_lost................ 0
Performance_schema_thread_instances_lost.............. 0
Prepared_stmt_count................................... 0
Qcache_free_blocks.................................... 3519
Qcache_free_memory.................................... 188066688
Qcache_hits........................................... 2317144
Qcache_inserts........................................ 1213446
Qcache_lowmem_prunes.................................. 0
Qcache_not_cached..................................... 4839
Qcache_queries_in_cache............................... 7418
Qcache_total_blocks................................... 18818
Queries............................................... 3602138
Questions............................................. 3602138
Rpl_status............................................ AUTH_MASTER
Select_full_join...................................... 1093
Select_full_range_join................................ 42
Select_range.......................................... 9238
Select_range_check.................................... 0
Select_scan........................................... 11861
Slave_heartbeat_period................................ 0.000
Slave_open_temp_tables................................ 0
Slave_received_heartbeats............................. 0
Slave_retried_transactions............................ 0
Slave_running......................................... OFF
Slow_launch_threads................................... 0
Slow_queries.......................................... 7
Sort_merge_passes..................................... 1842
Sort_range............................................ 536
Sort_rows............................................. 24889479
Sort_scan............................................. 1244
Ssl_accept_renegotiates............................... 0
Ssl_accepts........................................... 0
Ssl_callback_cache_hits............................... 0
Ssl_cipher............................................
Ssl_cipher_list.......................................
Ssl_client_connects................................... 0
Ssl_connect_renegotiates.............................. 0
Ssl_ctx_verify_depth.................................. 0
Ssl_ctx_verify_mode................................... 0
Ssl_default_timeout................................... 0
Ssl_finished_accepts.................................. 0
Ssl_finished_connects................................. 0
Ssl_session_cache_hits................................ 0
Ssl_session_cache_misses.............................. 0
Ssl_session_cache_mode................................ NONE
Ssl_session_cache_overflows........................... 0
Ssl_session_cache_size................................ 0
Ssl_session_cache_timeouts............................ 0
Ssl_sessions_reused................................... 0
Ssl_used_session_cache_entries........................ 0
Ssl_verify_depth...................................... 0
Ssl_verify_mode....................................... 0
Ssl_version...........................................
Table_locks_immediate................................. 1284452
Table_locks_waited.................................... 0
Tc_log_max_pages_used................................. 0
Tc_log_page_size...................................... 0
Tc_log_page_waits..................................... 0
Threads_cached........................................ 2
Threads_connected..................................... 31
Threads_created....................................... 33
Threads_running....................................... 1
Uptime................................................ 42918
Uptime_since_flush_status............................. 42918

GLOBAL VARIABLES:
auto_increment_increment.............................. 1
auto_increment_offset................................. 1
autocommit............................................ ON
automatic_sp_privileges............................... ON
back_log.............................................. 50
basedir............................................... C:/Program Files/MySQL/MySQL Server 5.5/
big_tables............................................ OFF
binlog_cache_size..................................... 32768
binlog_direct_non_transactional_updates............... OFF
binlog_format......................................... STATEMENT
binlog_stmt_cache_size................................ 32768
bulk_insert_buffer_size............................... 8388608
character_set_client.................................. latin1
character_set_connection.............................. latin1
character_set_database................................ latin1
character_set_filesystem.............................. binary
character_set_results................................. latin1
character_set_server.................................. latin1
character_set_system.................................. utf8
character_sets_dir.................................... C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\
collation_connection.................................. latin1_swedish_ci
collation_database.................................... latin1_swedish_ci
collation_server...................................... latin1_swedish_ci
completion_type....................................... NO_CHAIN
concurrent_insert..................................... AUTO
connect_timeout....................................... 10
datadir............................................... C:\Program Files\MySQL\MySQL Server 5.5\data\
date_format........................................... %Y-%m-%d
datetime_format....................................... %Y-%m-%d %H:%i:%s
default_storage_engine................................ InnoDB
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
expire_logs_days...................................... 0
flush................................................. OFF
flush_time............................................ 1800
foreign_key_checks.................................... ON
ft_boolean_syntax..................................... + -><()~*:""&|
ft_max_word_len....................................... 84
ft_min_word_len....................................... 4
ft_query_expansion_limit.............................. 20
ft_stopword_file...................................... (built-in)
general_log........................................... OFF
general_log_file...................................... C:\Program Files\MySQL\MySQL Server 5.5\data\cpsosrv-oracle.log
group_concat_max_len.................................. 1024
have_compress......................................... YES
have_crypt............................................ NO
have_csv.............................................. YES
have_dynamic_loading.................................. YES
have_geometry......................................... YES
have_innodb........................................... YES
have_ndbcluster....................................... NO
have_openssl.......................................... DISABLED
have_partitioning..................................... YES
have_profiling........................................ YES
have_query_cache...................................... YES
have_rtree_keys....................................... YES
have_ssl.............................................. DISABLED
have_symlink.......................................... YES
hostname.............................................. cpsosrv-oracle
ignore_builtin_innodb................................. OFF
init_connect..........................................
init_file.............................................
init_slave............................................
innodb_adaptive_flushing.............................. ON
innodb_adaptive_hash_index............................ ON
innodb_additional_mem_pool_size....................... 13631488
innodb_autoextend_increment........................... 8
innodb_autoinc_lock_mode.............................. 1
innodb_buffer_pool_instances.......................... 1
innodb_buffer_pool_size............................... 7516192768
innodb_change_buffering............................... all
innodb_checksums...................................... ON
innodb_commit_concurrency............................. 0
innodb_concurrency_tickets............................ 5000
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................................. OFF
innodb_flush_log_at_trx_commit........................ 1
innodb_flush_method...................................
innodb_force_load_corrupted........................... OFF
innodb_force_recovery................................. 0
innodb_io_capacity.................................... 200
innodb_large_prefix................................... OFF
innodb_lock_wait_timeout.............................. 120
innodb_locks_unsafe_for_binlog........................ OFF
innodb_log_buffer_size................................ 7340032
innodb_log_file_size.................................. 56623104
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_print_all_deadlocks............................ OFF
innodb_purge_batch_size............................... 20
innodb_purge_threads.................................. 0
innodb_random_read_ahead.............................. OFF
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............................. 34
innodb_thread_sleep_delay............................. 10000
innodb_use_native_aio................................. ON
innodb_use_sys_malloc................................. ON
innodb_version........................................ 5.5.36
innodb_write_io_threads............................... 4
interactive_timeout................................... 28800
join_buffer_size...................................... 131072
keep_files_on_create.................................. OFF
key_buffer_size....................................... 333447168
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
lc_messages........................................... en_US
lc_messages_dir....................................... C:\Program Files\MySQL\MySQL Server 5.5\share\
lc_time_names......................................... en_US
license............................................... GPL
local_infile.......................................... ON
lock_wait_timeout..................................... 31536000
log................................................... OFF
log_bin............................................... OFF
log_bin_trust_function_creators....................... OFF
log_error............................................. C:\Users\cor-pro_admin\Desktop\NAS.txt
log_output............................................ FILE
log_queries_not_using_indexes......................... OFF
log_slave_updates..................................... OFF
log_slow_queries...................................... OFF
log_warnings.......................................... 1
long_query_time....................................... 30.000000
low_priority_updates.................................. OFF
lower_case_file_system................................ ON
lower_case_table_names................................ 1
max_allowed_packet.................................... 1048576
max_binlog_cache_size................................. 18446744073709547520
max_binlog_size....................................... 1073741824
max_binlog_stmt_cache_size............................ 18446744073709547520
max_connect_errors.................................... 10
max_connections....................................... 122
max_delayed_threads................................... 20
max_error_count....................................... 64
max_heap_table_size................................... 16777216
max_insert_delayed_threads............................ 20
max_join_size......................................... 18446744073709551615
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..................................... 4294967295
max_sort_length....................................... 1024
max_sp_recursion_depth................................ 0
max_tmp_tables........................................ 32
max_user_connections.................................. 0
max_write_lock_count.................................. 4294967295
metadata_locks_cache_size............................. 1024
min_examined_row_limit................................ 0
multi_range_count..................................... 256
myisam_data_pointer_size.............................. 6
myisam_max_sort_file_size............................. 107374182400
myisam_mmap_size...................................... 18446744073709551615
myisam_recover_options................................ OFF
myisam_repair_threads................................. 1
myisam_sort_buffer_size............................... 429916160
myisam_stats_method................................... nulls_unequal
myisam_use_mmap....................................... OFF
named_pipe............................................ OFF
net_buffer_length..................................... 16384
net_read_timeout...................................... 30
net_retry_count....................................... 10
net_write_timeout..................................... 60
new................................................... OFF
old................................................... OFF
old_alter_table....................................... OFF
old_passwords......................................... OFF
open_files_limit...................................... 2692
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,engine_condition_pushdown=on
performance_schema.................................... OFF
performance_schema_events_waits_history_long_size..... 10000
performance_schema_events_waits_history_size.......... 10
performance_schema_max_cond_classes................... 80
performance_schema_max_cond_instances................. 1000
performance_schema_max_file_classes................... 50
performance_schema_max_file_handles................... 32768
performance_schema_max_file_instances................. 10000
performance_schema_max_mutex_classes.................. 200
performance_schema_max_mutex_instances................ 1000000
performance_schema_max_rwlock_classes................. 30
performance_schema_max_rwlock_instances............... 1000000
performance_schema_max_table_handles.................. 100000
performance_schema_max_table_instances................ 50000
performance_schema_max_thread_classes................. 50
performance_schema_max_thread_instances............... 1000
pid_file.............................................. C:\Program Files\MySQL\MySQL Server 5.5\data\cpsosrv-oracle.pid
plugin_dir............................................ C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\
port.................................................. 4306
preload_buffer_size................................... 32768
profiling............................................. OFF
profiling_history_size................................ 15
protocol_version...................................... 10
query_alloc_block_size................................ 8192
query_cache_limit..................................... 1048576
query_cache_min_res_unit.............................. 4096
query_cache_size...................................... 211812352
query_cache_type...................................... ON
query_cache_wlock_invalidate.......................... OFF
query_prealloc_size................................... 8192
range_alloc_block_size................................ 4096
read_buffer_size...................................... 65536
read_only............................................. OFF
read_rnd_buffer_size.................................. 262144
relay_log.............................................
relay_log_index.......................................
relay_log_info_file................................... relay-log.info
relay_log_purge....................................... ON
relay_log_recovery.................................... OFF
relay_log_space_limit................................. 0
report_host...........................................
report_password.......................................
report_port........................................... 4306
report_user...........................................
rpl_recovery_rank..................................... 0
secure_auth........................................... OFF
secure_file_priv......................................
server_id............................................. 0
shared_memory......................................... OFF
shared_memory_base_name............................... MYSQL
skip_external_locking................................. ON
skip_name_resolve..................................... OFF
skip_networking....................................... OFF
skip_show_database.................................... OFF
slave_compressed_protocol............................. OFF
slave_exec_mode....................................... STRICT
slave_load_tmpdir..................................... C:\TEMP
slave_max_allowed_packet.............................. 1073741824
slave_net_timeout..................................... 3600
slave_skip_errors..................................... OFF
slave_transaction_retries............................. 10
slave_type_conversions................................
slow_launch_time...................................... 2
slow_query_log........................................ OFF
slow_query_log_file................................... C:/Users/cor-pro_admin/Desktop/Slow Queries.txt
socket................................................ MySQL
sort_buffer_size...................................... 262144
sql_auto_is_null...................................... OFF
sql_big_selects....................................... ON
sql_big_tables........................................ OFF
sql_buffer_result..................................... OFF
sql_log_bin........................................... ON
sql_log_off........................................... OFF
sql_low_priority_updates.............................. OFF
sql_max_join_size..................................... 18446744073709551615
sql_mode.............................................. STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes............................................. ON
sql_quote_show_create................................. ON
sql_safe_updates...................................... OFF
sql_select_limit...................................... 18446744073709551615
sql_slave_skip_counter................................ 0
sql_warnings.......................................... OFF
ssl_ca................................................
ssl_capath............................................
ssl_cert..............................................
ssl_cipher............................................
ssl_key...............................................
storage_engine........................................ InnoDB
stored_program_cache.................................. 256
sync_binlog........................................... 0
sync_frm.............................................. ON
sync_master_info...................................... 0
sync_relay_log........................................ 0
sync_relay_log_info................................... 0
system_time_zone...................................... Central Daylight Time
table_definition_cache................................ 400
table_open_cache...................................... 256
thread_cache_size..................................... 8
thread_concurrency.................................... 10
thread_handling....................................... one-thread-per-connection
thread_stack.......................................... 262144
time_format........................................... %H:%i:%s
time_zone............................................. SYSTEM
timed_mutexes......................................... OFF
tmp_table_size........................................ 214958080
tmpdir................................................ C:/TEMP
transaction_alloc_block_size.......................... 8192
transaction_prealloc_size............................. 4096
tx_isolation.......................................... REPEATABLE-READ
unique_checks......................................... ON
updatable_views_with_limit............................ YES
version............................................... 5.5.36
version_comment....................................... MySQL Community Server (GPL)
version_compile_machine............................... x86
version_compile_os.................................... Win64
wait_timeout.......................................... 28800

SOLUTION

gheist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

First I'd find out how many different places the current queries exist. So you would know how many places you would have to update the queries after turning it into a 'normal' database that doesn't require 4 joins to get a single record.

JP_TechGroup

ASKER

Thanks for the feedback guys. I have already run a tuning script and did not get much that was helpful.
I have enabled slow query logging, but I am reasonably sure that most queries here are going to be slow!
Re-writing the queries so they will be faster id not really an option. How? My schema structure is what it is. Altering the database and the front end to change that is an enormous re-write of a project I took ownership of yesterday!

I'm screwed, aren't I?

gheist

slow queries is a programming error. Can you post a query or two with respective DDL?
16GB to hold full database in RAM is not expensive.
You will be wasting lots of CPU

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JP_TechGroup

ASKER

Thanks Dave. That was the conclusion I had come to as well. You hope that you don't know what you're talking about and throw it out there...

Dave Baldwin

You're welcome. I had a table like that... but at a couple of hundred rows performance wasn't a problem.

gheist

Lets attack where it hurts most with minimal rewrite of code:
Easiest:
select a.x where a.y in (whatever list) - obviously simplest index on y will make things roll.

If there is an index and full read happens try to sniff around if DB server ever/often crashed. ndexes might be invalidated over time, and reindexing would get them back to work.

Say in the case no query gets prepared you might want to add parentheses (or optimize ANDs and ORs to make less (ISAM) teable reads,)in select where or reorder _ JOIN to save time on query planner/optimizer running for each query. Usually you run EXPLAIN with query and try to re-arrange it to match optimizer result.
Other thing to search in slow query log : select a.* 0> it is wrong, listing fields in query is faster
SELECT queries dont change data, you can play around with them all the time.