Large table ignoring index?

Hey,

I’m hoping someone can help me here, currently, I’ve a large table

CREATE TABLE my_large_table ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, pname VARCHAR(512) NOT NULL, pvalue VARCHAR(64000) DEFAULT NULL, ptype VARCHAR(16) NOT NULL DEFAULT ‘unknown’, paccess VARCHAR(64000) DEFAULT NULL, anotherid BIGINT NOT NULL, INDEX idx_anotherid ( anotherid ))AUTO_INCREMENT = 1ENGINE = MyISAMDEFAULT CHARACTER SET utf8DEFAULT COLLATE utf8_bin;

and its just not using the index idx_anotherid when I do a query on anotherid

a simple
select * from my_large_table where anotherid = 2

will take FOREVER … it just hangs … doing an explain on it doesnt even show the index idx_anotherid as a possiblile index!

This query should return about 1000-3000 results
The table is approx 100million rows

I’ve tried using
‘use index(idx_anotherid)’ and ‘force index(idx_anotherid)’
with no luck

I had done a repair table quick … no luck

I’ve just set the max_seeks_for_key to 100 and am doing an optimize table on it (its taking forever) … will know the results of this in a few hours ughhh

Can anyone give any pointers?

Thanks
/Bal

Finished the optimize table with no joy :confused:

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 :confused:
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_sort_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 :confused:

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.