So this means we cannot combine both FULLTEXT and classical indexes if we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able to ?
How about being able to specify the indexes we want to load into the cache. It's supposed to work this way (but it is told in the doc it doesn't yet). This would solve the problem I believe, if we specify what index we want in cache. What I don't undestand is that when not cached using LOAD INDEX INTO CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too, and this does not see to cause any trouble. But using LOAD INDEX, it doesn't work. Is there really no workaround ? We have for about 1.5Go of fulltext indexes and if they were in cache, this would speed up things so much ! Thx for your advices HMax On Fri, 18 Feb 2005 18:01:29 +0200, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > 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] > > -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
