GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Large table ignoring index?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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)

    Comment


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

      Comment


      • #4
        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>

        Comment


        • #5
          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?

          Comment


          • #6
            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?

            Comment


            • #7
              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.

              Comment

              Working...
              X