Hello.
Sergei Golubchik said that we can't change the value of the blocksize of a key (it is chosen in mi_create.c) and there is no workaround with this LOAD INDEX problem. HMax <[EMAIL PROTECTED]> wrote: > 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=20 > `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=3DMyISAM DEFAULT CHARSET=3Dutf8 > > > 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=E9verine 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 Blocks= > ize > 1 1 4 unique unsigned long 1 1024 1= > 024 > 2 5 300 multip. char packed stripped 0 2048 2= > 048 > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
