Hi folks, I'm attempting to optimize a query -- it's quite a simple one, actually.
SELECT uid from Bill_Sales WHERE startDate > '[some date]'; mysql> show create table Bill_Sales\G *************************** 1. row *************************** Table: Bill_Sales Create Table: CREATE TABLE `Bill_Sales` ( `sales_id` int(4) unsigned NOT NULL auto_increment, `uid` int(10) unsigned NOT NULL default '0', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` timestamp NOT NULL default '0000-00-00 00:00:00', `startDate` date NOT NULL default '0000-00-00', `endDate` date NOT NULL default '0000-00-00', `typesale` enum('pos','void','chargeback','refunded') default NULL, PRIMARY KEY (`sales_id`), KEY `uid` (`uid`), KEY `startDate` (`startDate`,`endDate`,`typesale`), KEY `endDate` (`endDate`,`startDate`,`typesale`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-22'; +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL | NULL | NULL | 1028766 | Using where | +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-23'; +----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------------+ | 1 | SIMPLE | Bill_Sales | range | startDate | startDate | 3 | NULL | 192022 | Using where | +----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------------+ 1 row in set (0.00 sec) The cutoff date for using the index versus not using the index is around 2 months ago! This always happens on the production server, but I cannot get it to work if I recreate the table without all the data. However, we've replicated the data to a few machines, and the explains are consistent with the replicated data. So I run a REPAIR TABLE, which should fix the indexes. It definitely changed something, because now the cutoff date is about a week ago. mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-13"; +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL | NULL | NULL | 1028777 | Using where | +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-14"; +----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------------+ | 1 | SIMPLE | Bill_Sales | range | startDate | startDate | 3 | NULL | 190891 | Using where | +----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------------+ 1 row in set (0.00 sec) Why would the query do this? And why does it change when I run a repair table? (show status: mysql> show status; +----------------------------+------------+ | Variable_name | Value | +----------------------------+------------+ | Aborted_clients | 74279 | | Aborted_connects | 146 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 1163526992 | | Bytes_sent | 359522512 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table | 45 | | Com_analyze | 1 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 154039613 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 26 | | Com_dealloc_sql | 0 | | Com_delete | 15447773 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 27 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 2 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 10401852 | | Com_insert_select | 136718 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 1 | | Com_optimize | 2 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 1 | | Com_repair | 3 | | Com_replace | 4126896 | | Com_replace_select | 18322588 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 573190719 | | Com_set_option | 1181 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_column_types | 0 | | Com_show_create_db | 1 | | Com_show_create_table | 8 | | Com_show_databases | 10499 | | Com_show_errors | 0 | | Com_show_fields | 2540 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 2 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 24 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 21192 | | Com_show_status | 23851 | | Com_show_storage_engines | 0 | | Com_show_tables | 64075 | | Com_show_variables | 6 | | Com_show_warnings | 0 | | Com_slave_start | 12 | | Com_slave_stop | 0 | | Com_truncate | 100 | | Com_unlock_tables | 1 | | Com_update | 833306826 | | Com_update_multi | 0 | | Connections | 153975605 | | Created_tmp_disk_tables | 16 | | Created_tmp_files | 1544309 | | Created_tmp_tables | 11011061 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 2 | | Handler_commit | 45 | | Handler_delete | 395252 | | Handler_discover | 0 | | Handler_read_first | 191695 | | Handler_read_key | 717659723 | | Handler_read_next | 1440309480 | | Handler_read_prev | 0 | | Handler_read_rnd | 1055091498 | | Handler_read_rnd_next | 2185842449 | | Handler_rollback | 133982982 | | Handler_update | 9610342 | | Handler_write | 1511376494 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 56612 | | Key_blocks_used | 153316 | | Key_read_requests | 1615349579 | | Key_reads | 320440 | | Key_write_requests | 40643476 | | Key_writes | 21199977 | | Max_used_connections | 955 | | Not_flushed_delayed_rows | 0 | | Open_files | 101 | | Open_streams | 0 | | Open_tables | 345 | | Opened_tables | 1637 | | Qcache_free_blocks | 1289 | | Qcache_free_memory | 31947024 | | Qcache_hits | 3002872 | | Qcache_inserts | 181537 | | Qcache_lowmem_prunes | 150945 | | Qcache_not_cached | 573396839 | | Qcache_queries_in_cache | 788 | | Qcache_total_blocks | 2935 | | Questions | 1766422261 | | Rpl_status | NULL | | Select_full_join | 137 | | Select_full_range_join | 0 | | Select_range | 1139287 | | Select_range_check | 0 | | Select_scan | 2550 | | Slave_open_temp_tables | 0 | | Slave_running | ON | | Slave_retried_transactions | 0 | | Slow_launch_threads | 0 | | Slow_queries | 1019 | | Sort_merge_passes | 150 | | Sort_range | 2393850 | | Sort_rows | 1694680796 | | Sort_scan | 10551731 | | Table_locks_immediate | 1489264630 | | Table_locks_waited | 4822 | | Threads_cached | 127 | | Threads_connected | 1 | | Threads_created | 7558 | | Threads_running | 1 | | Uptime | 3593626 | +----------------------------+------------+ 157 rows in set (0.01 sec) ) my.cnf: # The MySQL server [mysqld] old-passwords skip-locking port = 3306 socket = /var/lib/mysql/mysql.sock key_buffer = 190M max_allowed_packet = 16M table_cache = 1600 sort_buffer_size = 4M read_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache = 128 ft_min_word_len = 3 tmp_table_size = 32M # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than "long_query_time" seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 interactive_timeout = 28800 wait_timeout = 30 max_connections = 1947 max_connect_errors = 128 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 32M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 128M innodb_log_buffer_size = 32M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 16 innodb_file_io_threads = 4 innodb_table_locks = 0 -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]