Re: Adding index kills query

[Robert Stafford]

Yeah, given the speed of the query with no index, it seems like something has gone serious wrong with the query that uses the index.

Here's the results of the "show variables like 'inno%';":

Variable_name Value
innodb_adaptive_flushing ON
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 3145728
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_instances 1
innodb_buffer_pool_size 112197632
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 2097152
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_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 10
innodb_thread_sleep_delay 10000
innodb_use_native_aio ON
innodb_use_sys_malloc ON
innodb_version 1.1.8
innodb_write_io_threads 4

And the results of the "show status like 'inno%';":
Variable_name Value
Innodb_buffer_pool_pages_data 6787
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 3185962
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_misc 61
Innodb_buffer_pool_pages_total 6848
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 9182987
Innodb_buffer_pool_read_ahead_evicted 4089865
Innodb_buffer_pool_read_requests 2292814693
Innodb_buffer_pool_reads 5763407
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 1137327510
Innodb_data_fsyncs 88393
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 70766592
Innodb_data_reads 15034796
Innodb_data_writes 3413399
Innodb_data_written 2143302144
Innodb_dblwr_pages_written 3185962
Innodb_dblwr_writes 32399
Innodb_have_atomic_builtins ON
Innodb_log_waits 2
Innodb_log_write_requests 119084594
Innodb_log_writes 73891
Innodb_os_log_fsyncs 22739
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 821447168
Innodb_page_size 16384
Innodb_pages_created 3175643
Innodb_pages_read 14946393
Innodb_pages_written 3185962
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 0
Innodb_rows_inserted 737577550
Innodb_rows_read 1192814613
Innodb_rows_updated 0
Innodb_truncated_status_writes 0

Fiddling with the engine settings is outside my experience, and these results don't mean much (approximately anything) to me. Let me know if you see anything that looks strange.