Thanx, that's exactly it. I feel a bit embarassed as this came up on
the list about 2-3 weeks ago, and I found the answer as I was waiting
for the replies.
-Sheeri
On 1/20/06, gerald_clark <[EMAIL PROTECTED]> wrote:
> sheeri kritzer wrote:
>
> >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
> >
> >
> >
> Once about 1/3 of the data matches, it is quicker to not use the index.
> This date seems to be the threshold.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
