Privet.
> SELECT SQL_CACHE id, program_name, categor, subcategor, subsubcategor,
> subsubsubcategor, source_url, rating, short_description, long_description,
> when_modifed+0, author, searched_words, size_in_kb, licence, language,
> install, win95, win98, winme, winnt, win2000, winxp, wince, unix, palm, mac,
> other_versions, rus_progs , MATCH (search_keywords ) AGAINST ('ÙÐßÛ xbÊÍ
> ÞÊÎÍ' ) as relevance FROM programms1 WHERE MATCH (search_keywords ) AGAINST
> ('+(>(ÙÐßÛ)) +(xbÊÍ) +(ÞÊÎÍ)' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT
> 1000;
I didn't done much research but first thing which comes to mind is that your
query has two potentially problematic places. The first one is a
FULLTEXT part, the second is ORDER BY clause. In my opinion the
index isn't used in the ordering (please, correct me if I'm wrong).
And MySQL uses a filesort algorithm. If a filesort must be done, all
rows that match the query without the LIMIT clause must be selected, and
most or all of them must be sorted, before it can be ascertained that
the first 1000 rows have been found. Use SHOW PROCESSLIST (mysqladmin
processlist, mytop) to find out in which states mysqld threads of the slow
queries are spending more time. In such a way we'll find the bottleneck in
the query.
Michael Monashev <[EMAIL PROTECTED]> wrote:
> Hello
>
> GP> Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
> GP> statements, amount of RAM, most problematic queries (use mysqldumpslow
> GP> utility to find them) include tables' definitions (use SHOW CREATE
> GP> TABLE).
>
> SHOW VARIABLES;
> back_log 50
> basedir /*************/
> binlog_cache_size 32768
> bulk_insert_buffer_size 8388608
> character_set_client utf8
> character_set_connection utf8
> character_set_database cp1251
> character_set_results utf8
> character_set_server cp1251
> character_set_system utf8
> character_sets_dir /***********/share/mysql/charsets/
> collation_connection utf8_general_ci
> collation_database cp1251_general_ci
> collation_server cp1251_general_ci
> concurrent_insert ON
> connect_timeout 5
> datadir /*************/
> 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 0
> 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 YES
> have_csv NO
> have_example_engine NO
> have_geometry YES
> have_innodb NO
> 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
> interactive_timeout 28800
> join_buffer_size 131072
> key_buffer_size 536870912
> key_cache_age_threshold 300
> key_cache_block_size 1024
> key_cache_division_limit 100
> language /*********/share/mysql/english/
> large_files_support ON
> license GPL
> local_infile ON
> locked_in_memory OFF
> log OFF
> log_bin OFF
> log_error /***********/error.log
> log_slave_updates OFF
> log_slow_queries ON
> log_update OFF
> log_warnings 1
> long_query_time 10
> low_priority_updates OFF
> lower_case_file_system OFF
> lower_case_table_names 0
> max_allowed_packet 1047552
> max_binlog_cache_size 4294967295
> max_binlog_size 1073741824
> max_connect_errors 10
> max_connections 300
> 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 2147483648
> myisam_max_sort_file_size 2147483647
> myisam_recover_options OFF
> myisam_repair_threads 1
> myisam_sort_buffer_size 67108864
> net_buffer_length 16384
> net_read_timeout 30
> net_retry_count 1000000
> net_write_timeout 60
> Variable_name Value
> new OFF
> old_passwords OFF
> open_files_limit 14781
> pid_file /**********/pid.file
> port 3306
> 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 16777216
> query_cache_type DEMAND
> query_cache_wlock_invalidate OFF
> query_prealloc_size 8192
> range_alloc_block_size 2048
> read_buffer_size 2093056
> read_only OFF
> read_rnd_buffer_size 8384512
> relay_log_purge ON
> rpl_recovery_rank 0
> secure_auth OFF
> server_id 1
> skip_external_locking ON
> skip_networking ON
> skip_show_database OFF
> slave_net_timeout 3600
> slow_launch_time 2
> socket /tmp/********.sock
> sort_buffer_size 2097144
> sql_mode
> storage_engine MyISAM
> sync_binlog 0
> sync_frm ON
> system_time_zone MSD
> table_cache 512
> table_type MyISAM
> thread_cache_size 8
> thread_stack 196608
> time_format %H:%i:%s
> time_zone SYSTEM
> tmp_table_size 33554432
> tmpdir
> transaction_alloc_block_size 8192
> transaction_prealloc_size 4096
> tx_isolation REPEATABLE-READ
> version 4.1.8-log
> version_comment Source distribution
> version_compile_machine i386
> version_compile_os unknown-freebsd5.2.1
> wait_timeout 28800
>
>
> SHOW STATUS;
> Aborted_clients 195
> Aborted_connects 161
> Binlog_cache_disk_use 0
> Binlog_cache_use 0
> Bytes_received 2375361016
> Bytes_sent 1259738696
> Com_admin_commands 2162461
> Com_alter_db 0
> Com_alter_table 25
> Com_analyze 0
> Com_backup_table 0
> Com_begin 0
> Com_change_db 778
> 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 17
> Com_dealloc_sql 0
> Com_delete 5199
> Com_delete_multi 0
> Com_do 0
> Com_drop_db 0
> Com_drop_function 0
> Com_drop_index 0
> Com_drop_table 34
> 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 6940
> Com_insert_select 17
> Com_kill 173
> Com_load 0
> Com_load_master_data 0
> Com_load_master_table 0
> Com_lock_tables 0
> Com_optimize 29
> Com_preload_keys 0
> Com_prepare_sql 0
> Com_purge 0
> Com_purge_before_date 0
> Com_rename_table 0
> Com_repair 2
> Com_replace 397685
> Com_replace_select 25162
> Com_reset 0
> Com_restore_table 0
> Com_revoke 0
> Com_revoke_all 0
> Com_rollback 0
> Com_savepoint 0
> Com_select 51204493
> Com_set_option 3255677
> Com_show_binlog_events 0
> Com_show_binlogs 614
> Com_show_charsets 724
> Com_show_collations 724
> Com_show_column_types 0
> Com_show_create_db 9
> Com_show_create_table 4
> Com_show_databases 3
> Com_show_errors 0
> Com_show_fields 132
> Com_show_grants 17
> Com_show_innodb_status 0
> Com_show_keys 35
> 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 592
> Com_show_slave_hosts 0
> Com_show_slave_status 0
> Com_show_status 4
> Com_show_storage_engines 0
> Com_show_tables 187
> Com_show_variables 1454
> Com_show_warnings 0
> Com_slave_start 0
> Com_slave_stop 0
> Com_truncate 0
> Com_unlock_tables 0
> Com_update 6198868
> Com_update_multi 0
> Connections 36942
> Created_tmp_disk_tables 28
> Created_tmp_files 6614
> Created_tmp_tables 2450
> Delayed_errors 0
> Delayed_insert_threads 1
> Delayed_writes 289693
> Variable_name Value
> Flush_commands 1
> Handler_commit 0
> Handler_delete 5145
> Handler_discover 0
> Handler_read_first 38054
> Handler_read_key 107135693
> Handler_read_next 987802812
> Handler_read_prev 17451987
> Handler_read_rnd 74706000
> Handler_read_rnd_next 15697149
> Handler_rollback 0
> Handler_update 7285852
> Handler_write 7629240
> Key_blocks_not_flushed 0
> Key_blocks_unused 209615
> Key_blocks_used 259217
> Key_read_requests 3265045094
> Key_reads 514495
> Key_write_requests 44032420
> Key_writes 28031516
> Max_used_connections 68
> Not_flushed_delayed_rows 0
> Open_files 366
> Open_streams 0
> Open_tables 329
> Opened_tables 1266
> Qcache_free_blocks 41
> Qcache_free_memory 222752
> Qcache_hits 403342
> Qcache_inserts 1142208
> Qcache_lowmem_prunes 1087690
> Qcache_not_cached 50062140
> Qcache_queries_in_cache 85
> Qcache_total_blocks 1224
> Questions 61539502
> Rpl_status NULL
> Select_full_join 0
> Select_full_range_join 0
> Select_range 12402776
> Select_range_check 0
> Select_scan 122732
> Slave_open_temp_tables 0
> Slave_running OFF
> Slow_launch_threads 14
> Slow_queries 8479
> Sort_merge_passes 3341
> Sort_range 1200657
> Sort_rows 102718275
> Sort_scan 4068
> Table_locks_immediate 57790378
> Table_locks_waited 72932
> Threads_cached 4
> Threads_connected 9
> Threads_created 9510
> Threads_running 1
> Uptime 721559
>
> amount of RAM:
> 4Gb RAM, Dual Xeon 2400, 3 x SCIS 140Gb
>
> most problematic queries:
> SELECT SQL_CACHE id, program_name, categor, subcategor, subsubcategor,
> subsubsubcategor, source_url, rating, short_description, long_description,
> when_modifed+0, author, searched_words, size_in_kb, licence, language,
> install, win95, win98, winme, winnt, win2000, winxp, wince, unix, palm, mac,
> other_versions, rus_progs , MATCH (search_keywords ) AGAINST ('ÙÐßÛ xbÊÍ
> ÞÊÎÍ' ) as relevance FROM programms1 WHERE MATCH (search_keywords ) AGAINST
> ('+(>(ÙÐßÛ)) +(xbÊÍ) +(ÞÊÎÍ)' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT
> 1000;
>
> table definition:
> CREATE TABLE programms1 (
> id int(10) unsigned NOT NULL auto_increment,
> categor tinyint(3) unsigned NOT NULL default '0',
> subcategor tinyint(3) unsigned NOT NULL default '0',
> subsubcategor tinyint(3) unsigned NOT NULL default '0',
> subsubsubcategor tinyint(3) unsigned NOT NULL default '0',
> program_name varchar(255) NOT NULL default '',
> language tinyint(3) unsigned NOT NULL default '0',
> install tinyint(3) unsigned NOT NULL default '0',
> win95 tinyint(3) unsigned NOT NULL default '0',
> win98 tinyint(3) unsigned NOT NULL default '0',
> winme tinyint(3) unsigned NOT NULL default '0',
> winnt tinyint(3) unsigned NOT NULL default '0',
> win2000 tinyint(3) unsigned NOT NULL default '0',
> winxp tinyint(3) unsigned NOT NULL default '0',
> wince tinyint(3) unsigned NOT NULL default '0',
> palm tinyint(3) unsigned NOT NULL default '0',
> mac tinyint(3) unsigned NOT NULL default '0',
> unix tinyint(3) unsigned NOT NULL default '0',
> licence tinyint(3) unsigned NOT NULL default '0',
> size_in_kb mediumint(8) unsigned NOT NULL default '0',
> author varchar(255) NOT NULL default '',
> short_description varchar(255) NOT NULL default '',
> long_description text NOT NULL,
> when_modifed timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> rating int(11) NOT NULL default '0',
> source_url varchar(255) NOT NULL default '',
> searched_words varchar(255) NOT NULL default '',
> letter char(1) character set cp1251 collate cp1251_bin NOT NULL default '',
> other_versions tinyint(3) unsigned NOT NULL default '0',
> rus_progs tinyint(3) unsigned NOT NULL default '0',
> search_keywords text NOT NULL,
> similar_keywords text NOT NULL,
> PRIMARY KEY (id),
> KEY rating (rating),
> KEY source_url (source_url),
> KEY when_modifed (when_modifed),
> KEY is_book (categor,rating),
> KEY letter (letter,program_name),
> KEY author (author,rating),
> KEY categor (categor,when_modifed),
> KEY subcategor (subcategor,rating),
> KEY subsubcategor (subsubcategor,rating),
> KEY subsubsubcategor (subsubsubcategor,rating),
> KEY subcategor1 (subcategor,when_modifed),
> KEY subsubcategor1 (subsubcategor,when_modifed),
> KEY subsubsubcategor1 (subsubsubcategor,when_modifed),
> KEY categor_2 (categor,language,rating),
> KEY categor_3 (categor,licence,rating),
> KEY categor_4 (categor,install,rating),
> KEY categor_5 (categor,win98,rating),
> KEY categor_6 (categor,win2000,rating),
> KEY categor_7 (categor,winxp,rating),
> KEY language (language,when_modifed),
> KEY licence (licence,when_modifed),
> KEY install (install,when_modifed),
> KEY win95 (win95,when_modifed),
> KEY win98 (win98,when_modifed),
> KEY winme (winme,when_modifed),
> KEY winnt (winnt,when_modifed),
> KEY win2000 (win2000,when_modifed),
> KEY winxp (winxp,when_modifed),
> KEY unix (unix,when_modifed),
> KEY wince (wince,when_modifed),
> KEY win98_2 (win98,rating),
> KEY licence_2 (licence,rating),
> KEY palm (palm,rating),
> KEY win95_2 (win95,rating),
> KEY mac (mac,rating),
> KEY winxp_2 (winxp,rating),
> KEY letter_2 (letter,rating),
> KEY winme_2 (winme,rating),
> KEY letter_3 (letter,language,rating),
> KEY palm_2 (palm,when_modifed),
> KEY language_2 (language,licence,rating),
> FULLTEXT KEY search_keywords (search_keywords),
> FULLTEXT KEY similar_keywords (similar_keywords)
> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 PACK_KEYS=1 CHECKSUM=1;
>
>
> Sincerely,
> Michael,
> http://xoib.com/ http://3d2f.com/
> http://qaix.com/ http://ryxi.com/
> http://gyxe.com/ http://gyxu.com/
> http://xywe.com/ http://xyqe.com/
>
>
>
>
--
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]
