Finished the optimize table with no joy :/


mysql> explain select anotherid from my_large_table where anotherid = 1821;+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+| 1 | SIMPLE | my_large_table | ALL | NULL | NULL | NULL | NULL | 103961138 | Using where |+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+1 row in set (0.00 sec)mysql>


adding use index


mysql> explain select anotherid from my_large_table use index(idx_anotherid) where anotherid = 1821;+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+| 1 | SIMPLE | my_large_table | ALL | NULL | NULL | NULL | NULL | 103961138 | Using where |+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+1 row in set (0.01 sec)


using force

mysql> explain select anotherid from my_large_table force index(idx_anotherid) where anotherid = 1821;+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+| 1 | SIMPLE | my_large_table | ALL | NULL | NULL | NULL | NULL | 103961138 | Using where |+----+-------------+------------------+------+---------------+------+---------+------+-----------+-------------+1 row in set (0.00 sec)

Trying to drop the index and recreate it now :/
Will update ya in a few hours (

mysql> show variables;+-----------------------------------------+-------------------------------------------------------------------------------------------+| Variable_name | Value |+-----------------------------------------+-------------------------------------------------------------------------------------------+| auto_increment_increment | 1 || auto_increment_offset | 1 || autocommit | ON || automatic_sp_privileges | ON || back_log | 128 || basedir | / || big_tables | OFF || binlog_cache_size | 32768 || binlog_direct_non_transactional_updates | OFF || binlog_format | STATEMENT || bulk_insert_buffer_size | 8388608 || character_set_client | latin1 || character_set_connection | latin1 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | latin1 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ || collation_connection | latin1_swedish_ci || collation_database | utf8_general_ci || collation_server | latin1_swedish_ci || completion_type | 0 || concurrent_insert | 1 || connect_timeout | 120 || datadir | /data/mysql/ || date_format | %Y-%m-%d || datetime_format | %Y-%m-%d %H:%i:%s || default_week_format | 0 || delay_key_write | ON || delayed_insert_limit | 100 || delayed_insert_timeout | 300 || delayed_queue_size | 1000 || div_precision_increment | 4 || engine_condition_pushdown | ON || error_count | 0 || event_scheduler | OFF || expire_logs_days | 5 || flush | OFF || flush_time | 0 || foreign_key_checks | ON || ft_boolean_syntax | + -><()~*:""&| || ft_max_word_len | 84 || ft_min_word_len | 4 || ft_query_expansion_limit | 20 || ft_stopword_file | (built-in) || general_log | OFF || general_log_file | /data/mysql/venus.log || group_concat_max_len | 1024 || have_community_features | YES || have_compress | YES || have_crypt | YES || have_csv | YES || have_dynamic_loading | YES || have_geometry | YES || have_innodb | DISABLED || have_ndbcluster | NO || have_openssl | DISABLED || have_partitioning | YES || have_query_cache | YES || have_rtree_keys | YES || have_ssl | DISABLED || have_symlink | YES || hostname | venus || identity | 0 || ignore_builtin_innodb | OFF || init_connect | SET AUTOCOMMIT=1 || init_file | || init_slave | || insert_id | 0 || interactive_timeout | 360 || join_buffer_size | 131072 || keep_files_on_create | OFF || key_buffer_size | 1073741824 || key_cache_age_threshold | 300 || key_cache_block_size | 1024 || key_cache_division_limit | 100 || language | /usr/share/mysql/english/ || large_files_support | ON || large_page_size | 0 || large_pages | OFF || last_insert_id | 0 || lc_time_names | en_US || license | GPL || local_infile | ON || locked_in_memory | OFF || log | OFF || log_bin | OFF || log_bin_trust_function_creators | OFF || log_bin_trust_routine_creators | OFF || log_error | /var/log/mysql/mysql-err.log || log_output | FILE || log_queries_not_using_indexes | OFF || log_slave_updates | OFF || log_slow_queries | ON || log_warnings | 1 || long_query_time | 3.000000 || low_priority_updates | OFF || lower_case_file_system | OFF || lower_case_table_names | 0 || max_allowed_packet | 5242880 || max_binlog_cache_size | 4294963200 || max_binlog_size | 1073741824 || max_connect_errors | 65535 || max_connections | 10000 || max_delayed_threads | 20 || max_error_count | 64 || max_heap_table_size | 16777216 || max_insert_delayed_threads | 20 || max_join_size | 18446744073709551615 || max_length_for_sort_data | 1024 || max_prepared_stmt_count | 16382 || max_relay_log_size | 0 || max_seeks_for_key | 100 || max_sort_length | 1024 || max_sp_recursion_depth | 0 || max_tmp_tables | 32 || max_user_connections | 0 || max_write_lock_count | 4294967295 || min_examined_row_limit | 0 || multi_range_count | 256 || myisam_data_pointer_size | 6 || myisam_max_sort_file_size | 1073741824 || myisam_mmap_size | 4294967295 || myisam_recover_options | OFF || myisam_repair_threads | 3 || myisam_sort_buffer_size | 134217728 || myisam_stats_method | nulls_unequal || myisam_use_mmap | OFF || net_buffer_length | 16384 || net_read_timeout | 120 || net_retry_count | 60 || net_write_timeout | 120 || new | OFF || old | OFF || old_alter_table | OFF || old_passwords | OFF || open_files_limit | 1024 || optimizer_prune_level | 1 || optimizer_search_depth | 62 || optimizer_switch | index_merge=on,index_merge_union=on,index_merge_so rt_union=on,index_merge_intersection=on || pid_file | /data/mysql/venus.pid || plugin_dir | /usr/lib/mysql/plugin || port | 3306 || preload_buffer_size | 32768 || profiling | OFF || profiling_history_size | 15 || protocol_version | 10 || pseudo_thread_id | 74 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 33554432 || query_cache_type | ON || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || rand_seed1 | || rand_seed2 | || range_alloc_block_size | 4096 || read_buffer_size | 786432 || read_only | OFF || read_rnd_buffer_size | 786432 || relay_log | || relay_log_index | || relay_log_info_file | relay-log.info || relay_log_purge | ON || relay_log_space_limit | 0 || report_host | || report_password | || report_port | 3306 || report_user | || rpl_recovery_rank | 0 || secure_auth | OFF || secure_file_priv | || server_id | 0 || skip_external_locking | ON || skip_name_resolve | OFF || skip_networking | OFF || skip_show_database | OFF || slave_compressed_protocol | OFF || slave_exec_mode | STRICT || slave_load_tmpdir | /var/tmp/ || slave_net_timeout | 3600 || slave_skip_errors | OFF || slave_transaction_retries | 10 || slow_launch_time | 2 || slow_query_log | ON || slow_query_log_file | /var/log/mysql/mysql-slow.log || socket | /var/lib/mysql/mysql.sock || sort_buffer_size | 1048576 || sql_auto_is_null | ON || sql_big_selects | ON || sql_big_tables | OFF || sql_buffer_result | OFF || sql_log_bin | ON || sql_log_off | OFF || sql_log_update | ON || sql_low_priority_updates | OFF || sql_max_join_size | 18446744073709551615 || sql_mode | || sql_notes | ON || sql_quote_show_create | ON || sql_safe_updates | OFF || sql_select_limit | 18446744073709551615 || sql_slave_skip_counter | || sql_warnings | OFF || ssl_ca | || ssl_capath | || ssl_cert | || ssl_cipher | || ssl_key | || storage_engine | MyISAM || sync_binlog | 0 || sync_frm | ON || system_time_zone | IST || table_definition_cache | 256 || table_lock_wait_timeout | 60 || table_open_cache | 20000 || table_type | MyISAM || thread_cache_size | 15 || thread_handling | one-thread-per-connection || thread_stack | 262144 || time_format | %H:%i:%s || time_zone | SYSTEM || timed_mutexes | OFF || timestamp | 1282743822 || tmp_table_size | 16777216 || tmpdir | /var/tmp/ || transaction_alloc_block_size | 8192 || transaction_prealloc_size | 4096 || tx_isolation | REPEATABLE-READ || unique_checks | ON || updatable_views_with_limit | YES || version | 5.1.47-community-log || version_comment | MySQL Community Server (GPL) || version_compile_machine | i686 || version_compile_os | pc-linux-gnu || wait_timeout | 360 || warning_count | 0 |+-----------------------------------------+-------------------------------------------------------------------------------------------+239 rows in set (0.00 sec)mysql>

Ok that worked ..
I'm miffed what happened in the first place :/


mysql> explain select anotherid from my_large_table where anotherid = 1821;+----+-------------+------------------+------+---------------+-----------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+------+---------------+-----------+---------+-------+------+-------------+| 1 | SIMPLE | my_large_table | ref | idx_anotherid | idx_anotherid | 8 | const | 2789 | Using index |+----+-------------+------------------+------+---------------+-----------+---------+-------+------+-------------+1 row in set (0.00 sec)


Why wouldn't

repair table my_large_table quick;
or
optimize table my_large_table;
or
analyze table my_large_table;

have worked?
The repair is meant to rebuild indexes?

This looks like a bug to me. You could file a bug report, but you're probably going to have a hard time reproducing it now, so the bug report will be useless.

PS: Did you intentionally disable InnoDB?

We disable innodb, apparently its recommended if you're not using it due to resources it can take up

In the infinate wisdom of some guys here, we're using myiasm instead of innodb.. even with my recommendations to move due to it being a high rate insert/update app..

Gotta love table locking.

In the same infinate wisdom, instead of using innodb, the huge table is being split into thousands and thousands of smaller tables .. mytable_ x 150,000

What a goddamn joke.
Some guy here read somewhere that its common practice and said google and the like use it as a strategy so everyone just ooooohhhhh'd and ignored me saying

Might create a thread just asking what people think of splitting a large table into thousands upon thousands of small tables.