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]
