Hello there. OK I'll paste the results of commands you asked right after my reply, because we found out where the problem comes from. The myisamchk command showed that the index on the VarChar has a block size of 2048 instead of 1024. However, when I turn this index to a FULLTEXT one, the block size is back to 1024, and the LOAD INDEX INTO CACHE works.
Now this is a problem because our huge table needs both our FULLTEXT indexes and some on VARCHAR fields too. At least we know where it comes from. Now, is there a fast solution ? We were waiting for this bug correction to study a release date for our application :/ Thank you, and here is the results : SHOW CREATE TABLE `tbltest`; CREATE TABLE `tbltest` ( `testid` int(10) unsigned NOT NULL auto_increment, `testvalue` varchar(100) NOT NULL default '', PRIMARY KEY (`testid`), KEY `BOB` (`testvalue`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 SHOW VARIABLES; +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | back_log | 50 | | basedir | D:\mysql\4.1\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | D:\mysql\4.1\share\charsets/ | | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | D:\mysql\4.1\Data\ | | 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 | | expire_logs_days | 0 | | flush | OFF | | flush_time | 1800 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | NO | | have_bdb | NO | | have_compress | YES | | have_crypt | NO | | have_csv | NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | DISABLED | | have_isam | NO | | have_ndbcluster | NO | | have_openssl | NO | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 2097152 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | | | innodb_data_home_dir | | | innodb_fast_shutdown | ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 10485760 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 10485760 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | D:\mysql\4.1\share\english\ | | large_files_support | ON | | license | GPL | | local_infile | ON | | log | OFF | | log_bin | OFF | | log_error | .\testserver.err | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_update | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 1 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 107374182400 | | myisam_max_sort_file_size | 107374182400 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 10485760 | | named_pipe | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 622 | | pid_file | D:\mysql\4.1\Data\testserver.pid| | port | 3307 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 8388608 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 61440 | | read_only | OFF | | read_rnd_buffer_size | 258048 | | relay_log_purge | ON | | rpl_recovery_rank | 0 | | secure_auth | OFF | | shared_memory | OFF | | shared_memory_base_name | MYSQL | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slow_launch_time | 2 | | sort_buffer_size | 262136 | | sql_mode | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_replication | 0 | | sync_replication_slave_id | 0 | | sync_replication_timeout | 0 | | sync_frm | ON | | system_time_zone | Paris, Madrid | | table_cache | 256 | | table_type | MyISAM | | thread_cache_size | 8 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 7340032 | | tmpdir | | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.10-nt | | version_comment | MySQL Community Edition (GPL) | | version_compile_machine | i32 | | version_compile_os | NT | | wait_timeout | 28800 | +---------------------------------+---------------------------------+ SHOW STATUS; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 486 | | Bytes_sent | 3759 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 2 | | 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 | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 0 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 0 | | Com_set_option | 2 | | 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 | 0 | | Com_show_create_table | 1 | | Com_show_databases | 1 | | Com_show_errors | 0 | | Com_show_fields | 2 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 0 | | 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 | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_storage_engines | 0 | | Com_show_tables | 2 | | Com_show_variables | 1 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 0 | | Com_update_multi | 0 | | Connections | 3 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 0 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_read_first | 2 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 10 | | Handler_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 8981 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | | Max_used_connections | 2 | | Not_flushed_delayed_rows | 0 | | Open_files | 2 | | Open_streams | 0 | | Open_tables | 1 | | Opened_tables | 12 | | Qcache_free_blocks | 1 | | Qcache_free_memory | 8379904 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | | Questions | 12 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 0 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Table_locks_immediate | 11 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_connected | 2 | | Threads_created | 2 | | Threads_running | 1 | | Uptime | 315 | +--------------------------+---------+ C:\Documents and Settings\Séverine MOREL>D:\mysql\4.1\bin\myisamchk.exe -d -v D: \mysql\4.1\data\test\tbltest MyISAM file: D:\mysql\4.1\data\test\tbltest Record format: Packed Character set: utf8_general_ci (33) File-version: 1 Creation time: 2005-02-16 11:23:24 Status: changed Auto increment key: 1 Last value: 10 Data records: 10 Deleted blocks: 0 Datafile parts: 10 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 440 Keyfile length: 4096 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 305 table description: Key Start Len Index Type Rec/key Root Blocksize 1 1 4 unique unsigned long 1 1024 1024 2 5 300 multip. char packed stripped 0 2048 2048 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
