Hello.
> doesn't work. Is there really no workaround ? We have for about 1.5Go MySQL will store used blocks in memory and minimize a disk I/O, if your key_buffer_size variable has a sutable value and you have enough RAM. You may use CACHE INDEX to assign a separate cache for your table and get more performance. HMax <[EMAIL PROTECTED]> wrote: > 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] >> >> > > -- 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]
