I am trying to execute this statement


select `Distance`, count(`Distance`) from `hamming_q22_sd6_11` group by `Distance`


My table has approximately 300 million rows.
When I look at the process list it says

copying to tmp table

and it stays like that for hours. Something is not right. These things should take seconds not hours. I am really desperate, what am I doing wrong?

Csaba

Can you post the output of:

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

please describe the hardware you are using.

Can you also post the EXPLAIN output for the affected queries as well as the schema for involved tables.

I am sorry for the long post.
Here are my variables

INNODB Engine

=====================================120827 9:11:30 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 15 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 46 1_second, 46 sleeps, 4 10_second, 6 background, 6 flushsrv_master_thread log flush and writes: 282----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 7379, signal count 7385Mutex spin waits 222, rounds 1677, OS waits 37RW-shared spins 7414, rounds 219974, OS waits 7261RW-excl spins 79, rounds 2365, OS waits 78Spin rounds per wait: 7.55 mutex, 29.67 RW-shared, 29.94 RW-excl--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: waiting for completed aio requests (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0440273 OS file reads, 47 OS file writes, 20 OS fsyncs1 pending preads, 0 pending pwrites144.92 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 144498467, node heap has 1 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 1442779337294Log flushed up to 1442779337294Last checkpoint at 1442779337294Max checkpoint age 869019772Checkpoint age target 841862905Modified age 0Checkpoint age 00 pending log writes, 0 pending chkp writes21 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 75010932736; in additional pool allocated 0Internal hash tables (constant factor + variable factor) Adaptive hash index 1156008336 (1155987736 + 20600) Page hash 72250072 (buffer pool 0 only) Dictionary cache 289515676 (288998768 + 516908) File system 102472 (82672 + 19800) Lock system 180624648 (180623896 + 752) Recovery system 0 (0 + 0)Dictionary memory allocated 516908Buffer pool size 4456447Buffer pool size, bytes 73014427648Free buffers 4016207Database pages 440239Old database pages 162530Modified db pages 0Pending reads 1Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 440237, created 1, written 29144.92 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 971 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 440239, unzip_LRU len: 0I/O sum[0]:cur[11], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue2 read views open inside InnoDB---OLDEST VIEW---Normal read viewRead view low limit trx n 1CD6A3ARead view up limit trx id 1CD6A3ARead view low limit trx id 1CD6A3ARead view individually stored trx ids:-----------------Main thread process no. 4871, id 140149481813760, state: flushing logNumber of rows inserted 0, updated 8, deleted 0, read 2379223000.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 37559.63 reads/s------------TRANSACTIONS------------Trx id counter 1CD6A3BPurge done for trx's n < 1CD6A37 undo n < 0History list length 1201LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 25, OS thread handle 0x7f88f45bf700, query id 773 localhost 127.0.0.1 rootSHOW ENGINE INNODB STATUS---TRANSACTION 1CD6A3A, ACTIVE 41 sec fetching rowsmysql tables in use 1, locked 0MySQL thread id 37, OS thread handle 0x7f88f4683700, query id 747 localhost root Copying to tmp tableSELECT `Distance`, count(*) FROM `hamming_q22_sd6_12` group by `Distance` LIMIT 0, 30Trx read view will not see trx with id >= 1CD6A3B, sees < 1CD6A3B----------------------------END OF INNODB MONITOR OUTPUT============================


Global Variables:


auto_increment_increment 1auto_increment_offset 1autocommit ONautomatic_sp_privileges ONback_log 50basedir /usrbig_tables OFFbinlog_cache_size 32768binlog_direct_non_transactional_updates OFFbinlog_format STATEMENTbinlog_stmt_cache_size 32768bulk_insert_buffer_size 8388608character_set_client latin1character_set_connection latin1character_set_database latin1character_set_filesystem binarycharacter_set_results latin1character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection latin1_swedish_cicollation_database latin1_swedish_cicollation_server latin1_swedish_cicompletion_type NO_CHAINconcurrent_insert AUTOconnect_timeout 10datadir /var/lib/mysql/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_storage_engine InnoDBdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000div_precision_increment 4enforce_storage_engine engine_condition_pushdown ONevent_scheduler OFFexpand_fast_index_creation OFFexpire_logs_days 10fast_index_creation ONflush OFFflush_time 0foreign_key_checks ONft_boolean_syntax + -><()~*:""&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)general_log OFFgeneral_log_file /var/lib/mysql/toto.loggroup_concat_max_len 1024have_compress YEShave_crypt YEShave_csv YEShave_dynamic_loading YEShave_geometry YEShave_innodb YEShave_ndbcluster NOhave_openssl DISABLEDhave_partitioning YEShave_profiling YEShave_query_cache YEShave_response_time_distribution YEShave_rtree_keys YEShave_ssl DISABLEDhave_symlink YEShostname totoignore_builtin_innodb OFFinit_connect init_file init_slave innodb_adaptive_flushing ONinnodb_adaptive_flushing_method estimateinnodb_adaptive_hash_index ONinnodb_adaptive_hash_index_partitions 1innodb_additional_mem_pool_size 8388608innodb_autoextend_increment 8innodb_autoinc_lock_mode 1innodb_blocking_buffer_pool_restore OFFinnodb_buffer_pool_instances 1innodb_buffer_pool_restore_at_startup 0innodb_buffer_pool_shm_checksum ONinnodb_buffer_pool_shm_key 0innodb_buffer_pool_size 73014444032innodb_change_buffering allinnodb_checkpoint_age_target 0innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_corrupt_table_action assertinnodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_dict_size_limit 0innodb_doublewrite ONinnodb_doublewrite_file innodb_fake_changes OFFinnodb_fast_checksum OFFinnodb_fast_shutdown 1innodb_file_format Antelopeinnodb_file_format_check ONinnodb_file_format_max Antelopeinnodb_file_per_table ONinnodb_flush_log_at_trx_commit 1innodb_flush_method O_DIRECTinnodb_flush_neighbor_pages areainnodb_force_load_corrupted OFFinnodb_force_recovery 0innodb_ibuf_accel_rate 100innodb_ibuf_active_contract 1innodb_ibuf_max_size 36507205632innodb_import_table_from_xtrabackup 0innodb_io_capacity 200innodb_kill_idle_transaction 0innodb_large_prefix OFFinnodb_lazy_drop_table 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_block_size 512innodb_log_buffer_size 8388608innodb_log_file_size 536870912innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 75innodb_max_purge_lag 0innodb_merge_sort_block_size 1048576innodb_mirrored_log_groups 1innodb_old_blocks_pct 37innodb_old_blocks_time 0innodb_open_files 300innodb_page_size 16384innodb_purge_batch_size 20innodb_purge_threads 1innodb_random_read_ahead OFFinnodb_read_ahead linearinnodb_read_ahead_threshold 56innodb_read_io_threads 4innodb_recovery_stats OFFinnodb_recovery_update_relay_log OFFinnodb_replication_delay 0innodb_rollback_on_timeout OFFinnodb_rollback_segments 128innodb_show_locks_held 10innodb_show_verbose_locks 0innodb_spin_wait_delay 6innodb_stats_auto_update 1innodb_stats_method nulls_equalinnodb_stats_on_metadata ONinnodb_stats_sample_pages 8innodb_stats_update_need_lock 1innodb_strict_mode OFFinnodb_support_xa ONinnodb_sync_spin_loops 30innodb_table_locks ONinnodb_thread_concurrency 0innodb_thread_concurrency_timer_based OFFinnodb_thread_sleep_delay 10000innodb_use_global_flush_log_at_trx_commit ONinnodb_use_native_aio ONinnodb_use_sys_malloc ONinnodb_use_sys_stats_table OFFinnodb_version 1.1.8-rel28.0innodb_write_io_threads 4interactive_timeout 28800join_buffer_size 131072keep_files_on_create OFFkey_buffer_size 33554432key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100large_files_support ONlarge_page_size 0large_pages OFFlc_messages en_USlc_messages_dir /usr/share/mysql/lc_time_names en_USlicense GPLlocal_infile ONlock_wait_timeout 31536000locked_in_memory OFFlog OFFlog_bin OFFlog_bin_trust_function_creators OFFlog_error /var/log/mysql/error.loglog_output FILElog_queries_not_using_indexes ONlog_slave_updates OFFlog_slow_admin_statements OFFlog_slow_filter log_slow_queries OFFlog_slow_rate_limit 1log_slow_rate_type sessionlog_slow_slave_statements OFFlog_slow_sp_statements ONlog_slow_verbosity log_warnings 1log_warnings_suppress long_query_time 10.000000low_priority_updates OFFlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 16777216max_binlog_cache_size 18446744073709547520max_binlog_size 1048576000max_binlog_stmt_cache_size 18446744073709547520max_connect_errors 1000000max_connections 100max_delayed_threads 20max_error_count 64max_heap_table_size 33554432max_insert_delayed_threads 20max_join_size 18446744073709551615max_length_for_sort_data 1024max_long_data_size 16777216max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 18446744073709551615max_sort_length 1024max_sp_recursion_depth 0max_tmp_tables 32max_user_connections 0max_write_lock_count 18446744073709551615metadata_locks_cache_size 1024min_examined_row_limit 0multi_range_count 256myisam_data_pointer_size 6myisam_max_sort_file_size 9223372036853727232myisam_mmap_size 18446744073709551615myisam_recover_options BACKUP,FORCEmyisam_repair_threads 1myisam_sort_buffer_size 8388608myisam_stats_method nulls_unequalmyisam_use_mmap OFFnet_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold OFFold_alter_table OFFold_passwords OFFopen_files_limit 8302optimizer_fix ONoptimizer_prune_level 1optimizer_search_depth 62optimizer_switch index_merge=on,index_merge_union=on,index_merge_so rt_union=on,index_merge_intersection=on,engine_con dition_pushdown=onperformance_schema OFFperformance_schema_events_waits_history_long_si ze 10000performance_schema_events_waits_history_size 10performance_schema_max_cond_classes 80performance_schema_max_cond_instances 1000performance_schema_max_file_classes 50performance_schema_max_file_handles 32768performance_schema_max_file_instances 10000performance_schema_max_mutex_classes 200performance_schema_max_mutex_instances 1000000performance_schema_max_rwlock_classes 30performance_schema_max_rwlock_instances 1000000performance_schema_max_table_handles 100000performance_schema_max_table_instances 50000performance_schema_max_thread_classes 50performance_schema_max_thread_instances 1000pid_file /var/lib/mysql/toto.pidplugin_dir /usr/lib/mysql/plugin/port 3306preload_buffer_size 32768profiling OFFprofiling_history_size 15protocol_version 10query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 0query_cache_strip_comments OFFquery_cache_type OFFquery_cache_wlock_invalidate OFFquery_prealloc_size 8192query_response_time_range_base 10query_response_time_stats OFFrange_alloc_block_size 4096read_buffer_size 131072read_only OFFread_rnd_buffer_size 16777216relay_log relay_log_index relay_log_info_file relay-log.inforelay_log_purge ONrelay_log_recovery OFFrelay_log_space_limit 0report_host report_password report_port 3306report_user rpl_recovery_rank 0secure_auth OFFsecure_file_priv server_id 0skip_external_locking ONskip_name_resolve OFFskip_networking OFFskip_show_database OFFslave_compressed_protocol OFFslave_exec_mode STRICTslave_load_tmpdir /tmpslave_max_allowed_packet 1073741824slave_net_timeout 3600slave_skip_errors OFFslave_transaction_retries 10slave_type_conversions slow_launch_time 2slow_query_log OFFslow_query_log_file /var/lib/mysql/toto-slow.logslow_query_log_timestamp_always OFFslow_query_log_timestamp_precision secondslow_query_log_use_global_control socket /var/run/mysqld/mysqld.socksort_buffer_size 16777216sql_auto_is_null OFFsql_big_selects ONsql_big_tables OFFsql_buffer_result OFFsql_log_bin ONsql_log_off OFFsql_low_priority_updates OFFsql_max_join_size 18446744073709551615sql_mode sql_notes ONsql_quote_show_create ONsql_safe_updates OFFsql_select_limit 18446744073709551615sql_slave_skip_counter 0sql_warnings OFFssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDBstored_program_cache 256sync_binlog 0sync_frm ONsync_master_info 0sync_relay_log 0sync_relay_log_info 0system_time_zone MDTtable_definition_cache 4096table_open_cache 4096thread_cache_size 50thread_concurrency 10thread_handling one-thread-per-connectionthread_stack 196608thread_statistics OFFtime_format %H:%i:%stime_zone SYSTEMtimed_mutexes OFFtmp_table_size 33554432tmpdir /tmptransaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READunique_checks ONupdatable_views_with_limit YESuserstat OFFversion 5.5.27-28.0version_comment Percona Server (GPL), Release 28.0version_compile_machine x86_64version_compile_os Linuxwait_timeout 28800


Global Status:


Aborted_clients 0Aborted_connects 0Binlog_cache_disk_use 0Binlog_cache_use 0Binlog_stmt_cache_disk_use 0Binlog_stmt_cache_use 0Bytes_received 59802Bytes_sent 1621369Com_admin_commands 0Com_assign_to_keycache 0Com_alter_db 0Com_alter_db_upgrade 0Com_alter_event 0Com_alter_function 0Com_alter_procedure 0Com_alter_server 0Com_alter_table 0Com_alter_tablespace 0Com_analyze 0Com_begin 0Com_binlog 0Com_call_procedure 0Com_change_db 19Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 0Com_create_event 0Com_create_function 0Com_create_index 0Com_create_procedure 0Com_create_server 0Com_create_table 0Com_create_trigger 0Com_create_udf 0Com_create_user 0Com_create_view 0Com_dealloc_sql 0Com_delete 0Com_delete_multi 0Com_do 0Com_drop_db 0Com_drop_event 0Com_drop_function 0Com_drop_index 0Com_drop_procedure 0Com_drop_server 0Com_drop_table 0Com_drop_trigger 0Com_drop_user 0Com_drop_view 0Com_empty_query 0Com_execute_sql 0Com_flush 0Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 1Com_insert_select 0Com_install_plugin 0Com_kill 0Com_load 0Com_lock_tables 0Com_optimize 0Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_release_savepoint 0Com_rename_table 0Com_rename_user 0Com_repair 0Com_replace 5Com_replace_select 0Com_reset 0Com_resignal 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_rollback_to_savepoint 0Com_savepoint 0Com_select 212Com_set_option 75Com_signal 0Com_show_authors 0Com_show_binlog_events 0Com_show_binlogs 2Com_show_charsets 0Com_show_client_statistics 0Com_show_collations 0Com_show_contributors 0Com_show_create_db 0Com_show_create_event 0Com_show_create_func 2Com_show_create_proc 0Com_show_create_table 2Com_show_create_trigger 0Com_show_databases 4Com_show_engine_logs 0Com_show_engine_mutex 0Com_show_engine_status 4Com_show_events 0Com_show_errors 0Com_show_fields 3Com_show_function_status 1Com_show_grants 0Com_show_index_statistics 0Com_show_keys 2Com_show_master_status 1Com_show_open_tables 0Com_show_plugins 14Com_show_privileges 0Com_show_procedure_status 1Com_show_processlist 386Com_show_profile 0Com_show_profiles 0Com_show_relaylog_events 0Com_show_slave_hosts 0Com_show_slave_status 1Com_show_slave_status_nolock 0Com_show_status 20Com_show_storage_engines 0Com_show_table_statistics 0Com_show_table_status 5Com_show_tables 5Com_show_temporary_tables 0Com_show_thread_statistics 0Com_show_triggers 0Com_show_user_statistics 0Com_show_variables 8Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_fetch 0Com_stmt_prepare 0Com_stmt_reprepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Com_uninstall_plugin 0Com_unlock_tables 0Com_update 3Com_update_multi 0Com_xa_commit 0Com_xa_end 0Com_xa_prepare 0Com_xa_recover 0Com_xa_rollback 0Com_xa_start 0Compression OFFConnections 38Created_tmp_disk_tables 22Created_tmp_files 6Created_tmp_tables 92Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flashcache_enabled OFFFlush_commands 1Handler_commit 51Handler_delete 0Handler_discover 0Handler_prepare 0Handler_read_first 109Handler_read_key 241933609Handler_read_last 1Handler_read_next 70Handler_read_prev 1Handler_read_rnd 138Handler_read_rnd_next 241943359Handler_rollback 0Handler_savepoint 0Handler_savepoint_rollback 0Handler_update 241933119Handler_write 10727Innodb_adaptive_hash_cells 144498467Innodb_adaptive_hash_heap_buffers 0Innodb_adaptive_hash_hash_searches 0Innodb_adaptive_hash_non_hash_searches 2711Innodb_background_log_sync 282Innodb_buffer_pool_pages_data 455679Innodb_buffer_pool_pages_dirty 0Innodb_buffer_pool_pages_flushed 29Innodb_buffer_pool_pages_LRU_flushed 0Innodb_buffer_pool_pages_free 4000767Innodb_buffer_pool_pages_made_not_young 0Innodb_buffer_pool_pages_made_young 0Innodb_buffer_pool_pages_misc 1Innodb_buffer_pool_pages_old 168229Innodb_buffer_pool_pages_total 4456447Innodb_buffer_pool_read_ahead_rnd 0Innodb_buffer_pool_read_ahead 431312Innodb_buffer_pool_read_ahead_evicted 0Innodb_buffer_pool_read_requests 31603694Innodb_buffer_pool_reads 24366Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 76Innodb_checkpoint_age 0Innodb_checkpoint_max_age 869019772Innodb_checkpoint_target_age 841862905Innodb_data_fsyncs 20Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 1Innodb_data_pending_writes 0Innodb_data_read 7468027904Innodb_data_reads 455713Innodb_data_writes 47Innodb_data_written 968704Innodb_dblwr_pages_written 29Innodb_dblwr_writes 2Innodb_deadlocks 0Innodb_dict_tables 92Innodb_have_atomic_builtins ONInnodb_history_list_length 1201Innodb_ibuf_discarded_delete_marks 0Innodb_ibuf_discarded_deletes 0Innodb_ibuf_discarded_inserts 0Innodb_ibuf_free_list 0Innodb_ibuf_merged_delete_marks 0Innodb_ibuf_merged_deletes 0Innodb_ibuf_merged_inserts 0Innodb_ibuf_merges 0Innodb_ibuf_segment_size 2Innodb_ibuf_size 1Innodb_log_waits 0Innodb_log_write_requests 26Innodb_log_writes 12Innodb_lsn_current 1442779337294Innodb_lsn_flushed 1442779337294Innodb_lsn_last_checkpoint 1442779337294Innodb_master_thread_1_second_loops 46Innodb_master_thread_10_second_loops 4Innodb_master_thread_background_loops 6Innodb_master_thread_main_flush_loops 6Innodb_master_thread_sleeps 46Innodb_max_trx_id 30239291Innodb_mem_adaptive_hash 1156008336Innodb_mem_dictionary 289515676Innodb_mem_total 75010932736Innodb_mutex_os_waits 37Innodb_mutex_spin_rounds 1677Innodb_mutex_spin_waits 222Innodb_oldest_view_low_limit_trx_id 30239290Innodb_os_log_fsyncs 16Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 16384Innodb_page_size 16384Innodb_pages_created 1Innodb_pages_read 455677Innodb_pages_written 29Innodb_purge_trx_id 30239287Innodb_purge_undo_no 0Innodb_row_lock_current_waits 0Innodb_current_row_locks 0Innodb_row_lock_time 0Innodb_row_lock_time_avg 0Innodb_row_lock_time_max 0Innodb_row_lock_waits 0Innodb_rows_deleted 0Innodb_rows_inserted 0Innodb_rows_read 241932540Innodb_rows_updated 8Innodb_s_lock_os_waits 7261Innodb_s_lock_spin_rounds 219974Innodb_s_lock_spin_waits 7414Innodb_truncated_status_writes 0Innodb_x_lock_os_waits 78Innodb_x_lock_spin_rounds 2365Innodb_x_lock_spin_waits 79Key_blocks_not_flushed 0Key_blocks_unused 26774Key_blocks_used 18Key_read_requests 554Key_reads 18Key_write_requests 0Key_writes 0Last_query_cost 0.000000Max_used_connections 7Not_flushed_delayed_rows 0Open_files 66Open_streams 0Open_table_definitions 127Open_tables 158Opened_files 456Opened_table_definitions 127Opened_tables 165Performance_schema_cond_classes_lost 0Performance_schema_cond_instances_lost 0Performance_schema_file_classes_lost 0Performance_schema_file_handles_lost 0Performance_schema_file_instances_lost 0Performance_schema_locker_lost 0Performance_schema_mutex_classes_lost 0Performance_schema_mutex_instances_lost 0Performance_schema_rwlock_classes_lost 0Performance_schema_rwlock_instances_lost 0Performance_schema_table_handles_lost 0Performance_schema_table_instances_lost 0Performance_schema_thread_classes_lost 0Performance_schema_thread_instances_lost 0Prepared_stmt_count 0Qcache_free_blocks 0Qcache_free_memory 0Qcache_hits 0Qcache_inserts 0Qcache_lowmem_prunes 0Qcache_not_cached 0Qcache_queries_in_cache 0Qcache_total_blocks 0Queries 808Questions 808Rpl_status AUTH_MASTERSelect_full_join 1Select_full_range_join 0Select_range 9Select_range_check 0Select_scan 102Slave_heartbeat_period 0.000Slave_open_temp_tables 0Slave_received_heartbeats 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 40Sort_merge_passes 0Sort_range 0Sort_rows 159Sort_scan 19Ssl_accept_renegotiates 0Ssl_accepts 0Ssl_callback_cache_hits 0Ssl_cipher Ssl_cipher_list Ssl_client_connects 0Ssl_connect_renegotiates 0Ssl_ctx_verify_depth 0Ssl_ctx_verify_mode 0Ssl_default_timeout 0Ssl_finished_accepts 0Ssl_finished_connects 0Ssl_session_cache_hits 0Ssl_session_cache_misses 0Ssl_session_cache_mode NONESsl_session_cache_overflows 0Ssl_session_cache_size 0Ssl_session_cache_timeouts 0Ssl_sessions_reused 0Ssl_used_session_cache_entries 0Ssl_verify_depth 0Ssl_verify_mode 0Ssl_version Table_locks_immediate 260Table_locks_waited 0Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 1Threads_connected 6Threads_created 7Threads_running 2Uptime 2255Uptime_since_flush_status 2255binlog_commits 0binlog_group_commits 0

Server:
4 Quad-core AMD CPU
80 GB RAM
RAID 6 hard disks
Linux UBUNTU 12.04

I apologize,I modified my post after sending it, could you also post the output of

SHOW CREATE TABLE table\G
(where table is the table from SELECT COUNT(*) FROM table)

EXPLAIN select `Distance`, count(`Distance`) from `hamming_q22_sd6_11` group by `Distance`
SHOW CREATE TABLE hamming_q22_sd6_11\G

Thanks for your help!

SD6 table

CREATE TABLE `hamming_q22_sd6_12` ( `id1` int(10) unsigned NOT NULL, `id2` int(10) unsigned NOT NULL, `seq1` varchar(50) NOT NULL, `seq2` varchar(50) NOT NULL, `Distance` varchar(25) DEFAULT NULL, `length` smallint(6) NOT NULL, PRIMARY KEY (`id1`,`id2`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT


Explain statement


id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE hamming_q22_sd6_11 ALL NULL NULL NULL NULL 118203335 Using temporary; Using filesort

You should probably add an index on `Distance`

ALTER TABLE hamming_q22_sd6_12 ADD INDEX(Distance);

May also want to watch the following webinars:
http://www.percona.com/webinars/2012-08-15-mysql-indexing-be st-practices
http://www.percona.com/webinars/2012-02-22-explain-demystifi ed/

Also the following manual entries are relevant:
http://dev.mysql.com/doc/refman/5.5/...g-explain.html
https://dev.mysql.com/doc/refman/5.5...l-indexes.html

I would expect better performance out of SELECT COUNT(*) as well.... Innodb can be bad for SELECT COUNT(*) with no WHERE clause, however 9 hours seems suspect to me. even with a billion and a half rows.

Can you also put the EXPLAIN for the SELECT COUNT(*) on here?

I am tryin to add the index to the table. I am sure it will take a while. Here is the explain you requested:

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE hamming_q22_sd6_12 ALL NULL NULL NULL NULL 245387653 Using temporary; Using filesort


Is it possible that it's the underlying filesystem that makes these queries this slow?

doubtful its the filesystem.

I'm surprised its not at least reading the PK as they are NOT NULL.
There's probably some caveat in the optimizer i'm forgetting/unaware of.

I assume you have a dev/test system to benchmark these on. I would toy with adding an index on id1.

Also, debate what you really need out of SELECT COUNT(*).
Does it need to accurate 100% of the time? Or can we fudge it?

What response time would you _like_ to see out of it ?

I am trying to add the index to the table. It has been running now for over 90 minutes. I don't know when it will finish.
I would like to have a reasonable response time.

I have just written a small python script that does the same things as the SQL statement. I dumped my table into an csv file and loaded into python. It finished in 5 seconds with 40 million records.
I would expect that databases would be more efficient than Python.

I am really baffled by this.

Index adds require a rebuild of the table (for the most part: See Innodb fast index in the manual).

Im not sure your doing an apples to apples comparison when comparing python.

Also if you just dumped that file into a CSV you just filled the linux page cache with it.

For the record, the reason Innodb can be slow for SELECT COUNT(*) is due to how B Trees work and Innodb's implementation of ACID.

http://en.wikipedia.org/wiki/Multiversion_concurrency_contro l

If you could, let me know how those index(es) work out.