为什么这个查询需要这么长时间？
我有一个复杂的查询需要在 MySQL 服务器上运行。
在我的家用测试机（Mac）上，查询报告需要 0.00 秒并立即返回结果。
然而，在我的服务器（一个 ubuntu 盒子）上，使用相同的数据进行相同的查询大约需要 3.30 分钟。
数据库使用InnoDB表。 ubuntu机器有64位内核，mysql是为此编译的。
为什么 ubuntu 服务器执行查询需要花费如此长的时间？
这些是 ubuntu-box 中的配置变量：
mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_danish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 10 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | DISABLED |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | databaseserver |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 1073741824 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 1 |
| 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_connect_errors | 10 |
| max_connections | 100 |
| 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_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 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 1 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | |
| port | 3306 |
| 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 | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| 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_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.75-0ubuntu10.5-log |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------+
这是查询：
SELECT work.id work \
, count( DISTINCT( u.id ) ) number_of_editions \
, GROUP_CONCAT( DISTINCT( u.main_title ) ) main_title \
, GROUP_CONCAT( DISTINCT( main_author.full_name ) ) main_author \
, CONCAT_WS(' ', \
GROUP_CONCAT( DISTINCT( u.sub_title_1 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_2 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_3 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_4 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_5 ) ) \
,GROUP_CONCAT( DISTINCT( u.alternative_title ) ) \
,GROUP_CONCAT( DISTINCT( sst.title ) ) \
) boktitler \
, GROUP_CONCAT( DISTINCT( a.full_name ) ) authorname \
, GROUP_CONCAT( DISTINCT( lp.name )) literary_award \
, GROUP_CONCAT( DISTINCT( f.name )) publisher \
, GROUP_CONCAT( DISTINCT( st.title )) series \
, GROUP_CONCAT( DISTINCT( otn.time )) about_period \
, GROUP_CONCAT( DISTINCT( ostn.place )) about_place \
, GROUP_CONCAT( DISTINCT( opn.full_name )) about_person \
, GROUP_CONCAT( DISTINCT( eon.tag )) utag \
, GROUP_CONCAT( DISTINCT( pt.title )) parallell_title \
, work.owners number_of_owners \
FROM core_work work \
LEFT \
JOIN core_edition u \
ON u.work_id = work.id \
LEFT \
JOIN core_author main_author \
ON main_author.edition_id = u.id AND main_author.nr = 1 \
LEFT \
JOIN core_conjoined_title sst \
ON sst.edition_id = u.id \
LEFT \
JOIN core_parallell_title pt \
ON pt.edition_id = u.id \
LEFT \
JOIN core_editiontag eo \
ON eo.edition_id = u.id \
LEFT \
JOIN core_utag eon \
ON eon.id = eo.tag_id \
LEFT \
JOIN core_edition_about_persons op \
ON op.edition_id = u.id \
LEFT \
JOIN core_about_person opn \
ON opn.id = op.about_person_id \
LEFT \
JOIN core_edition_about_place ost \
ON ost.edition_id = u.id \
LEFT \
JOIN core_about_place ostn \
ON ostn.id = ost.about_place_id \
LEFT \
JOIN core_edition_abouttime ot \
ON ot.edition_id = u.id \
LEFT \
JOIN core_abouttime otn \
ON otn.id = ot.abouttime_id \
LEFT \
JOIN core_seriesnr vs \
ON vs.edition_id = u.id \
LEFT \
JOIN core_series st \
ON st.id = vs.series_id \
LEFT \
JOIN core_edition_publisher uf \
ON uf.edition_id = u.id \
LEFT \
JOIN core_publisher f \
ON f.id = uf.publisher_id \
LEFT \
JOIN core_author a \
ON a.edition_id = u.id AND a.nr > 1 \
LEFT \
JOIN core_awarded_literary_award tlp \
ON tlp.edition_id = u.id \
LEFT \
JOIN core_literary_award lp \
ON tlp.literary_award_id = lp.id \
LEFT \
JOIN core_source source \
ON u.source_id = source.id \
WHERE u.hidden = 0 \
AND number_of_editions > 0 \
GROUP \
BY work.id \
LIMIT 1;
两个数据库中的数据应该相同（我从一个数据库转储到文件并恢复到另一个数据库）。当我执行 EXPLAIN SELECT 时，答案之间存在差异：（相同的行被编辑掉）
快速测试机器：
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+------+-------------+
| 1 | SIMPLE | work | index | PRIMARY,work_number_of_editions | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+------+-------------+
慢速机器：
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+--------+-----------------------------+
| 1 | SIMPLE | work | range | PRIMARY,work_number_of_editions | work_number_of_editions | 5 | NULL | 106796 | Using where; Using filesort |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+--------+-----------------------------+
这让我困惑；当 ubuntu 服务器与其他服务器具有相同的数据时，为什么它会报告 106796 行？为什么慢机器要使用文件排序？
I have a complex query I need to run on a MySQL-server.
On my home testing box, a mac, the query takes a reported 0.00 seconds and returns the reslut instantly.
On my server however, an ubuntu box, the same query takes around 3.30 minutes, using the same data.
The database uses InnoDB tables. The ubuntu box has a 64-bit kernel, and mysql is compiled for this.
Why does the ubuntu server take so much longer time to execute the query?
These are the configuration variables from the ubuntu-box:
mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_danish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 10 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | DISABLED |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | databaseserver |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 1073741824 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 1 |
| 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_connect_errors | 10 |
| max_connections | 100 |
| 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_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 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 1 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | |
| port | 3306 |
| 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 | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| 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_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.75-0ubuntu10.5-log |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------+
This is the query:
SELECT work.id work \
, count( DISTINCT( u.id ) ) number_of_editions \
, GROUP_CONCAT( DISTINCT( u.main_title ) ) main_title \
, GROUP_CONCAT( DISTINCT( main_author.full_name ) ) main_author \
, CONCAT_WS(' ', \
GROUP_CONCAT( DISTINCT( u.sub_title_1 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_2 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_3 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_4 ) ) \
,GROUP_CONCAT( DISTINCT( u.sub_title_5 ) ) \
,GROUP_CONCAT( DISTINCT( u.alternative_title ) ) \
,GROUP_CONCAT( DISTINCT( sst.title ) ) \
) boktitler \
, GROUP_CONCAT( DISTINCT( a.full_name ) ) authorname \
, GROUP_CONCAT( DISTINCT( lp.name )) literary_award \
, GROUP_CONCAT( DISTINCT( f.name )) publisher \
, GROUP_CONCAT( DISTINCT( st.title )) series \
, GROUP_CONCAT( DISTINCT( otn.time )) about_period \
, GROUP_CONCAT( DISTINCT( ostn.place )) about_place \
, GROUP_CONCAT( DISTINCT( opn.full_name )) about_person \
, GROUP_CONCAT( DISTINCT( eon.tag )) utag \
, GROUP_CONCAT( DISTINCT( pt.title )) parallell_title \
, work.owners number_of_owners \
FROM core_work work \
LEFT \
JOIN core_edition u \
ON u.work_id = work.id \
LEFT \
JOIN core_author main_author \
ON main_author.edition_id = u.id AND main_author.nr = 1 \
LEFT \
JOIN core_conjoined_title sst \
ON sst.edition_id = u.id \
LEFT \
JOIN core_parallell_title pt \
ON pt.edition_id = u.id \
LEFT \
JOIN core_editiontag eo \
ON eo.edition_id = u.id \
LEFT \
JOIN core_utag eon \
ON eon.id = eo.tag_id \
LEFT \
JOIN core_edition_about_persons op \
ON op.edition_id = u.id \
LEFT \
JOIN core_about_person opn \
ON opn.id = op.about_person_id \
LEFT \
JOIN core_edition_about_place ost \
ON ost.edition_id = u.id \
LEFT \
JOIN core_about_place ostn \
ON ostn.id = ost.about_place_id \
LEFT \
JOIN core_edition_abouttime ot \
ON ot.edition_id = u.id \
LEFT \
JOIN core_abouttime otn \
ON otn.id = ot.abouttime_id \
LEFT \
JOIN core_seriesnr vs \
ON vs.edition_id = u.id \
LEFT \
JOIN core_series st \
ON st.id = vs.series_id \
LEFT \
JOIN core_edition_publisher uf \
ON uf.edition_id = u.id \
LEFT \
JOIN core_publisher f \
ON f.id = uf.publisher_id \
LEFT \
JOIN core_author a \
ON a.edition_id = u.id AND a.nr > 1 \
LEFT \
JOIN core_awarded_literary_award tlp \
ON tlp.edition_id = u.id \
LEFT \
JOIN core_literary_award lp \
ON tlp.literary_award_id = lp.id \
LEFT \
JOIN core_source source \
ON u.source_id = source.id \
WHERE u.hidden = 0 \
AND number_of_editions > 0 \
GROUP \
BY work.id \
LIMIT 1;
The data in the two databases should be identical (I dumped to file from one and restored to the other). When I do EXPLAIN SELECT, there is a difference between the answers: (Identical rows edited out)
Fast testing machine:
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+------+-------------+
| 1 | SIMPLE | work | index | PRIMARY,work_number_of_editions | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+------+-------------+
Slow machine:
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+--------+-----------------------------+
| 1 | SIMPLE | work | range | PRIMARY,work_number_of_editions | work_number_of_editions | 5 | NULL | 106796 | Using where; Using filesort |
+----+-------------+-------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------------------+--------+-----------------------------+
This puzzles me; why does the ubuntu server report 106796 rows when it has the same data as the other? And why does the slow machine use filesort?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尝试
OPTIMIZE和ANALYZE所有表格Try to
OPTIMIZEandANALYZEall the tables尝试使用
EXPLAIN SELECT ...< /a> 在两台服务器上。这将显示正在使用哪些索引以及如何使用。如果两台机器上的设置有任何差异，它应该会影响查询计划（例如，服务器上的某些表缺少索引）。两台机器上的数据集是否相同？如果您的家用计算机上只有一小部分数据，而服务器上有完整的“较大”数据集，这也会产生影响。您使用了很多 DISTINCT 修饰符和 GROUP_CONCATS 并连接了大量的表。这可能需要使用相当数量的临时表，并且可能会超出可用内存，迫使 MySQL 使用磁盘缓冲，这与内存中操作相比非常慢。
Try to use
EXPLAIN SELECT ...on both servers. That will show which indexes are being used and how. If there's any differences in the setup on both machines, it should affect the query plan (e.g. some table is missing an index on your server).Are the data sets on both machines the same? If you only have a small subset of the data on your home machine, and the full "larger" dataset on the server, that would also affect things. You're using a lot of DISTINCT modifiers and GROUP_CONCATS and joining a large number of tables. This could require the use of a fair number of temporary tables and you may be exceeding the available memory, forcing MySQL to use disk buffering, which is very slow compared to in-memory operations.