针对关键查询优化 my.ini

我有一个带有相当复杂的仪表板查询的数据库系统。首先是细节。

数据模型：

DROP TABLE IF EXISTS `contract`;
CREATE TABLE IF NOT EXISTS `contract` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) NOT NULL,
  `code` varchar(3) NOT NULL,
  `moh_code` varchar(20) NOT NULL,
  `moh_number` varchar(20) NOT NULL,
  `moh_variation` varchar(20) NOT NULL,
  `description` mediumtext NOT NULL,
  `start` datetime NOT NULL,
  `finish` datetime NOT NULL,
  `cities` text,
  `is_support_contract` tinyint(1) DEFAULT NULL,
  `is_intensive` tinyint(1) DEFAULT NULL,
  `moh_team_type` varchar(4) DEFAULT NULL,
  `moh_team_setting` varchar(1) DEFAULT NULL,
  `moh_service_type` varchar(2) DEFAULT NULL,
  `moh_target_population` int(4) DEFAULT NULL,
  `moh_facility_id` varchar(10) DEFAULT NULL,
  `moh_open_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `organisation_id_idx` (`organisation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_engagement`
--

DROP TABLE IF EXISTS `peer_engagement`;
CREATE TABLE IF NOT EXISTS `peer_engagement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `peer_id` int(11) NOT NULL,
  `ps_number_id` int(11) NOT NULL,
  `service_organisation_id` int(11) DEFAULT NULL,
  `past_service_disengaged` datetime DEFAULT NULL,
  `key_worker_id` int(11) DEFAULT NULL,
  `iss_clinical_service_id` int(11) DEFAULT NULL,
  `iss_psychiatrist_id` int(11) DEFAULT NULL,
  `iss_service_coordinator_id` int(11) DEFAULT NULL,
  `iss_declined_courier_back_address` varchar(255) DEFAULT NULL,
  `waitinglist_support_suggestions_made` mediumtext,
  `waiting_psw_id` int(11) DEFAULT NULL,
  `declined_waiting_what_support_avail` mediumtext,
  `referral_source_id` int(11) DEFAULT NULL,
  `iss_referred_by` int(11) DEFAULT NULL,
  `record_entered_by` int(11) NOT NULL,
  `record_entered` datetime NOT NULL,
  `waiting_list_priority_id` int(11) DEFAULT NULL,
  `assigned_psw_id` int(11) DEFAULT NULL,
  `assigned` datetime DEFAULT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `assigned_effective` datetime DEFAULT NULL,
  `last_reengaged` datetime DEFAULT NULL,
  `last_reengaged_id` int(11) DEFAULT NULL,
  `disengagement_started` datetime DEFAULT NULL,
  `disengagement_target` datetime DEFAULT NULL,
  `disengagement_type_id` int(11) DEFAULT NULL,
  `disengagement_comments` mediumtext,
  `status_id` int(11) DEFAULT NULL,
  `closed` datetime DEFAULT NULL,
  `closed_by` int(11) DEFAULT NULL,
  `is_intensive` varchar(5) DEFAULT NULL,
  `heard_about_us_id` int(11) DEFAULT NULL,
  `primhd_referral_no` varchar(50) DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `is_gp_only` tinyint(4) NOT NULL,
  `referral_to_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `peer_id_idx` (`peer_id`),
  KEY `ps_number_id_idx` (`ps_number_id`),
  KEY `service_organisation_id_idx` (`service_organisation_id`),
  KEY `key_worker_id_idx` (`key_worker_id`),
  KEY `iss_clinical_service_id_idx` (`iss_clinical_service_id`),
  KEY `iss_psychiatrist_id_idx` (`iss_psychiatrist_id`),
  KEY `iss_service_coordinator_id_idx` (`iss_service_coordinator_id`),
  KEY `referral_source_id_idx` (`referral_source_id`),
  KEY `iss_referred_by_idx` (`iss_referred_by`),
  KEY `record_entered_by_idx` (`record_entered_by`),
  KEY `waiting_psw_id_idx` (`waiting_psw_id`),
  KEY `waiting_list_priority_id_idx` (`waiting_list_priority_id`),
  KEY `assigned_psw_id_idx` (`assigned_psw_id`),
  KEY `assigned_by_idx` (`assigned_by`),
  KEY `last_reengaged_id_idx` (`last_reengaged_id`),
  KEY `disengagement_type_id_idx` (`disengagement_type_id`),
  KEY `status_id_idx` (`status_id`),
  KEY `closed_by_idx` (`closed_by`),
  KEY `peer_engagement_heard_about_us_id_fk` (`heard_about_us_id`),
  KEY `moved_to_and_delete_id_foreign_key` (`moved_to_and_delete_id`),
  KEY `deleted_date` (`deleted_date`),
  KEY `disengagement_target` (`disengagement_target`),
  KEY `peer_engagement_referral_to_id_fk` (`referral_to_id`),
  KEY `is_intensive` (`is_intensive`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_number`
--

DROP TABLE IF EXISTS `peer_number`;
CREATE TABLE IF NOT EXISTS `peer_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ps_number` varchar(5) NOT NULL,
  `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `contract_id_idx` (`contract_id`),
  KEY `ps_number` (`ps_number`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75981 ;

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `suite` varchar(100) DEFAULT NULL,
  `hnr_street` varchar(200) NOT NULL,
  `suburb` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postcode` varchar(5) NOT NULL,
  `manual_address_entry` tinyint(1) DEFAULT NULL,
  `current_living_situation_id` int(11) DEFAULT NULL,
  `dhb_area_id` int(11) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone_daytime` varchar(25) DEFAULT NULL,
  `phone_evening` varchar(25) DEFAULT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `emergency_contact_details` text,
  `notes` mediumtext,
  `dob` date DEFAULT NULL,
  `nhi` varchar(7) DEFAULT NULL,
  `gender_id` int(11) DEFAULT NULL,
  `ethnicity_id` int(11) DEFAULT NULL,
  `team_leader_id` int(11) DEFAULT NULL,
  `organisation_id` int(11) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `postal_address` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `employment_situation_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `current_living_situation_id_idx` (`current_living_situation_id`),
  KEY `dhb_area_id_idx` (`dhb_area_id`),
  KEY `gender_id_idx` (`gender_id`),
  KEY `team_leader_id_idx` (`team_leader_id`),
  KEY `organisation_id_idx` (`organisation_id`),
  KEY `moved_to_and_delete_id_foreign_key_person` (`moved_to_and_delete_id`),
  KEY `person_employment_situation_id_fk` (`employment_situation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2377 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contract`
--
ALTER TABLE `contract`
  ADD CONSTRAINT `contract_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`);

--
-- Constraints for table `peer_engagement`
--
ALTER TABLE `peer_engagement`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `peer_engagement` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_by_person_id` FOREIGN KEY (`assigned_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_psw_id_person_id` FOREIGN KEY (`assigned_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_closed_by_person_id` FOREIGN KEY (`closed_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_disengagement_type_id_disengagement_type_id` FOREIGN KEY (`disengagement_type_id`) REFERENCES `disengagement_type` (`id`),
  ADD CONSTRAINT `peer_engagement_heard_about_us_id_fk` FOREIGN KEY (`heard_about_us_id`) REFERENCES `heard_about_us` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_clinical_service_id_organisation_id` FOREIGN KEY (`iss_clinical_service_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_psychiatrist_id_person_id` FOREIGN KEY (`iss_psychiatrist_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_referred_by_person_id` FOREIGN KEY (`iss_referred_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_service_coordinator_id_person_id` FOREIGN KEY (`iss_service_coordinator_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_key_worker_id_person_id` FOREIGN KEY (`key_worker_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_last_reengaged_id_peer_number_id` FOREIGN KEY (`last_reengaged_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_peer_id_person_id` FOREIGN KEY (`peer_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_ps_number_id_peer_number_id` FOREIGN KEY (`ps_number_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_record_entered_by_person_id` FOREIGN KEY (`record_entered_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_source_id_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_source` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_to_id_fk` FOREIGN KEY (`referral_to_id`) REFERENCES `referral_to` (`id`),
  ADD CONSTRAINT `peer_engagement_service_organisation_id_organisation_id` FOREIGN KEY (`service_organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_status_id_status_id` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  ADD CONSTRAINT `peer_engagement_waiting_psw_id_person_id` FOREIGN KEY (`waiting_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `pwwi` FOREIGN KEY (`waiting_list_priority_id`) REFERENCES `waiting_list_priority` (`id`);

--
-- Constraints for table `peer_number`
--
ALTER TABLE `peer_number`
  ADD CONSTRAINT `peer_number_contract_id_contract_id` FOREIGN KEY (`contract_id`) REFERENCES `contract` (`id`);

--
-- Constraints for table `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key_person` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `person_current_living_situation_id_current_living_situation_id` FOREIGN KEY (`current_living_situation_id`) REFERENCES `current_living_situation` (`id`),
  ADD CONSTRAINT `person_dhb_area_id_contract_id` FOREIGN KEY (`dhb_area_id`) REFERENCES `contract` (`id`),
  ADD CONSTRAINT `person_employment_situation_id_fk` FOREIGN KEY (`employment_situation_id`) REFERENCES `employment_situation` (`id`),
  ADD CONSTRAINT `person_gender_id_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`),
  ADD CONSTRAINT `person_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `person_team_leader_id_person_id` FOREIGN KEY (`team_leader_id`) REFERENCES `person` (`id`);

询问：

SELECT p.id AS p__id, p.peer_id AS p__peer_id, p.ps_number_id AS p__ps_number_id, p.service_organisation_id AS p__service_organisation_id, p.past_service_disengaged AS p__past_service_disengaged, p.key_worker_id AS p__key_worker_id, p.is_gp_only AS p__is_gp_only, p.iss_clinical_service_id AS p__iss_clinical_service_id, p.iss_psychiatrist_id AS p__iss_psychiatrist_id, p.iss_service_coordinator_id AS p__iss_service_coordinator_id, p.iss_declined_courier_back_address AS p__iss_declined_courier_back_address, p.waitinglist_support_suggestions_made AS p__waitinglist_support_suggestions_made, p.waiting_psw_id AS p__waiting_psw_id, p.declined_waiting_what_support_avail AS p__declined_waiting_what_support_avail, p.referral_source_id AS p__referral_source_id, p.iss_referred_by AS p__iss_referred_by, p.record_entered_by AS p__record_entered_by, p.record_entered AS p__record_entered, p.waiting_list_priority_id AS p__waiting_list_priority_id, p.assigned_psw_id AS p__assigned_psw_id, p.assigned AS p__assigned, p.assigned_by AS p__assigned_by, p.assigned_effective AS p__assigned_effective, p.last_reengaged AS p__last_reengaged, p.last_reengaged_id AS p__last_reengaged_id, p.disengagement_started AS p__disengagement_started, p.disengagement_target AS p__disengagement_target, p.disengagement_type_id AS p__disengagement_type_id, p.disengagement_comments AS p__disengagement_comments, p.status_id AS p__status_id, p.closed AS p__closed, p.closed_by AS p__closed_by, p.is_intensive AS p__is_intensive, p.heard_about_us_id AS p__heard_about_us_id, p.primhd_referral_no AS p__primhd_referral_no, p.moved_to_and_delete_id AS p__moved_to_and_delete_id, p.deleted_date AS p__deleted_date, p.referral_to_id AS p__referral_to_id, p2.id AS p2__id, p2.user_id AS p2__user_id, p2.name AS p2__name, p2.suite AS p2__suite, p2.hnr_street AS p2__hnr_street, p2.suburb AS p2__suburb, p2.city AS p2__city, p2.postcode AS p2__postcode, p2.manual_address_entry AS p2__manual_address_entry, p2.postal_address AS p2__postal_address, p2.current_living_situation_id AS p2__current_living_situation_id, p2.employment_situation_id AS p2__employment_situation_id, p2.dhb_area_id AS p2__dhb_area_id, p2.email AS p2__email, p2.phone_daytime AS p2__phone_daytime, p2.phone_evening AS p2__phone_evening, p2.mobile AS p2__mobile, p2.fax AS p2__fax, p2.emergency_contact_details AS p2__emergency_contact_details, p2.notes AS p2__notes, p2.dob AS p2__dob, p2.nhi AS p2__nhi, p2.gender_id AS p2__gender_id, p2.team_leader_id AS p2__team_leader_id, p2.start_date AS p2__start_date, p2.organisation_id AS p2__organisation_id, p2.role AS p2__role, p2.type AS p2__type, p2.moved_to_and_delete_id AS p2__moved_to_and_delete_id, p2.deleted_date AS p2__deleted_date, p3.id AS p3__id, p3.user_id AS p3__user_id, p3.name AS p3__name, p3.suite AS p3__suite, p3.hnr_street AS p3__hnr_street, p3.suburb AS p3__suburb, p3.city AS p3__city, p3.postcode AS p3__postcode, p3.manual_address_entry AS p3__manual_address_entry, p3.postal_address AS p3__postal_address, p3.current_living_situation_id AS p3__current_living_situation_id, p3.employment_situation_id AS p3__employment_situation_id, p3.dhb_area_id AS p3__dhb_area_id, p3.email AS p3__email, p3.phone_daytime AS p3__phone_daytime, p3.phone_evening AS p3__phone_evening, p3.mobile AS p3__mobile, p3.fax AS p3__fax, p3.emergency_contact_details AS p3__emergency_contact_details, p3.notes AS p3__notes, p3.dob AS p3__dob, p3.nhi AS p3__nhi, p3.gender_id AS p3__gender_id, p3.team_leader_id AS p3__team_leader_id, p3.start_date AS p3__start_date, p3.organisation_id AS p3__organisation_id, p3.role AS p3__role, p3.type AS p3__type, p3.moved_to_and_delete_id AS p3__moved_to_and_delete_id, p3.deleted_date AS p3__deleted_date, p4.id AS p4__id, p4.ps_number AS p4__ps_number, p4.contract_id AS p4__contract_id, c.id AS c__id, c.organisation_id AS c__organisation_id, c.code AS c__code, c.moh_code AS c__moh_code, c.moh_team_type AS c__moh_team_type, c.moh_team_setting AS c__moh_team_setting, c.moh_service_type AS c__moh_service_type, c.moh_target_population AS c__moh_target_population, c.moh_facility_id AS c__moh_facility_id, c.moh_open_date AS c__moh_open_date, c.moh_number AS c__moh_number, c.moh_variation AS c__moh_variation, c.description AS c__description, c.start AS c__start, c.finish AS c__finish, c.cities AS c__cities, c.is_support_contract AS c__is_support_contract, c.is_intensive AS c__is_intensive, COALESCE(p2.name, p3.name) AS p2__0 
FROM peer_engagement p 
LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
INNER JOIN contract c ON p4.contract_id = c.id 
INNER JOIN person p5 ON p.peer_id = p5.id 
WHERE (p.is_intensive = 1 AND p.status_id in (0,1,2,3,4,5,7) 

解释声明

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   ref peer_id_idx,ps_number_id_idx,status_id_idx,is_intensive,is_intensive_2  is_intensive    8   const   139 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1   

配置是默认的 Ubuntu 12.04：

auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
back_log    50
basedir /usr
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    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
completion_type NO_CHAIN
concurrent_insert   AUTO
connect_timeout 10
datadir /var/lib/mysql/
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
error_count 0
event_scheduler OFF
expire_logs_days    10
external_user   
flush   OFF
flush_time  0
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    /var/lib/mysql/mindandbody.log
group_concat_max_len    1024
have_compress   YES
have_crypt  YES
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    mindandbody
identity    0
ignore_builtin_innodb   OFF
init_connect    
init_file   
init_slave  
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 134217728
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   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    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
Variable_name   Value
innodb_log_file_size    5242880
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   0
innodb_thread_sleep_delay   10000
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.5.37
innodb_write_io_threads 4
insert_id   0
interactive_timeout 28800
join_buffer_size    131072
keep_files_on_create    OFF
key_buffer_size 16777216
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
last_insert_id  0
lc_messages en_US
lc_messages_dir /usr/share/mysql/english/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error   
log_output  FILE
log_queries_not_using_indexes   ON
log_slave_updates   OFF
log_slow_queries    OFF
log_warnings    1
long_query_time 10.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet  16777216
max_binlog_cache_size   18446744073709547520
max_binlog_size 104857600
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  10
max_connections 60
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  16777216
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   18446744073709551615
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    18446744073709551615
metadata_locks_cache_size   1024
min_examined_row_limit  0
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   9223372036853727232
myisam_mmap_size    18446744073709551615
myisam_recover_options  BACKUP
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length   16384
net_read_timeout    30
net_retry_count 10
net_write_timeout   60
Variable_name   Value
new OFF
old OFF
old_alter_table OFF
old_passwords   OFF
open_files_limit    1024
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch    index_merge=on,index_merge_union=on,index_merge_so...
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    /var/run/mysqld/mysqld.pid
plugin_dir  /usr/lib/mysql/plugin/
port    3306
preload_buffer_size 32768
profiling   OFF
profiling_history_size  15
protocol_version    10
proxy_user  
pseudo_slave_mode   OFF
pseudo_thread_id    205661
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    16777216
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size 8192
rand_seed1  0
rand_seed2  0
range_alloc_block_size  4096
read_buffer_size    131072
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 3306
report_user 
rpl_recovery_rank   0
secure_auth OFF
secure_file_priv    
server_id   0
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   /tmp
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 /var/lib/mysql/mindandbody-slow.log
socket  /var/run/mysqld/mysqld.sock
sort_buffer_size    2097152
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    
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
Variable_name   Value
sync_binlog 0
sync_frm    ON
sync_master_info    0
sync_relay_log  0
sync_relay_log_info 0
system_time_zone    NZDT
table_definition_cache  400
table_open_cache    256
thread_cache_size   8
thread_concurrency  10
thread_handling one-thread-per-connection
thread_stack    196608
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
timestamp   1441749665
tmp_table_size  16777216
tmpdir  /tmp
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit  YES
version 5.5.37-0ubuntu0.12.04.1
version_comment (Ubuntu)
version_compile_machine x86_64
version_compile_os  debian-linux-gnu
wait_timeout    28800
warning_count   0

我如何确定我的任何 my.ini 设置（或缺少设置）是否不必要地降低了我的查询速度？