Realized I should probably show the Bill_Sales table. . .
ls -lh Bill_Sales.*
-rw-rw---- 1 mysql mysql 104M Jan 20 15:11 Bill_Sales.MYD
-rw-rw---- 1 mysql mysql 97M Jan 20 15:11 Bill_Sales.MYI
-rw-rw---- 1 mysql mysql 9.2K Jan 3 13:43 Bill_Sales.frm
mysql> show table status like "Bill_Sales"\G
*************************** 1. row ***************************
Name: Bill_Sales
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 1028800
Avg_row_length: 105
Data_length: 108068128
Max_data_length: 4294967295
Index_length: 100814848
Data_free: 0
Auto_increment: 1058746
Create_time: 2006-01-03 13:43:04
Update_time: 2006-01-20 15:10:31
Check_time: 2006-01-20 14:48:01
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
On 1/20/06, sheeri kritzer <[EMAIL PROTECTED]> 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
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
