Re: How to optimize fulltext selection?
Hello Thank you so much. I Just increase the size of some buffers: # The MySQL server [mysqld] default-character-set = cp1251 port= 3306 socket = /x/xx.sock basedir = /x datadir = /x/x log-error = /x/error.log log-slow-queries = /x/slow.log pid-file = /x/pid.file key_buffer = 512M #was 384 max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 8M # was 4 read_buffer_size = 8M # was 4 read_rnd_buffer_size = 32M # was 16 myisam_sort_buffer_size = 128M # was 64 thread_cache = 8 query_cache_size= 16M query_cache_type = 2 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections=300 skip-locking skip-name-resolve skip-innodb skip-networking server-id = 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello GP What state is SHOW PROCESSLIST reporting for this query? Fulltext initialization 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello GP And MySQL uses a filesort algorithm. How to make the filesort faster? May be I have to increase size of some buffers? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
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_log50 basedir /*/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_clientutf8 character_set_connectionutf8 character_set_database cp1251 character_set_results utf8 character_set_servercp1251 character_set_systemutf8 character_sets_dir /***/share/mysql/charsets/ collation_connectionutf8_general_ci collation_database cp1251_general_ci collation_servercp1251_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_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 expire_logs_days0 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit20 ft_stopword_file(built-in) group_concat_max_len1024 have_archiveNO have_bdbNO have_compress YES have_crypt YES have_csvNO have_example_engine NO have_geometry YES have_innodb NO have_isam NO have_ndbcluster NO have_opensslNO have_query_cacheYES have_raid NO have_rtree_keys YES have_symlinkYES init_connect init_file init_slave interactive_timeout 28800 join_buffer_size131072 key_buffer_size 536870912 key_cache_age_threshold 300 key_cache_block_size1024 key_cache_division_limit100 language/*/share/mysql/english/ large_files_support ON license GPL local_infileON locked_in_memoryOFF log OFF log_bin OFF log_error /***/error.log log_slave_updates OFF log_slow_queriesON log_update OFF log_warnings1 long_query_time 10 low_priority_updatesOFF 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_data1024 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables 32 max_user_connections0 max_write_lock_count4294967295 myisam_data_pointer_size4 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_timeout30 net_retry_count 100 net_write_timeout 60 Variable_name Value new OFF old_passwords OFF open_files_limit14781 pid_file/**/pid.file port3306 preload_buffer_size 32768 protocol_version10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit4096 query_cache_size16777216 query_cache_typeDEMAND query_cache_wlock_invalidateOFF query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size2093056 read_only OFF read_rnd_buffer_size8384512 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_time2 socket /tmp/.sock sort_buffer_size2097144 sql_mode storage_engine MyISAM sync_binlog 0 sync_frmON system_time_zoneMSD table_cache 512 table_type MyISAM thread_cache_size 8 thread_stack196608 time_format %H:%i:%s time_zone SYSTEM tmp_table_size 33554432 tmpdir transaction_alloc_block_size8192 transaction_prealloc_size 4096 tx_isolationREPEATABLE-READ version 4.1.8-log version_comment Source distribution version_compile_machine i386 version_compile_os unknown-freebsd5.2.1 wait_timeout28800 SHOW STATUS; Aborted_clients 195 Aborted_connects161 Binlog_cache_disk_use 0 Binlog_cache_use0 Bytes_received 2375361016 Bytes_sent 1259738696 Com_admin_commands 2162461 Com_alter_db0 Com_alter_table 25 Com_analyze 0 Com_backup_table0 Com_begin 0 Com_change_db 778 Com_change_master 0 Com_check 0 Com_checksum0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index0 Com_create_table
How to optimize fulltext selection?
Hello, I have 200-300 kb slow log daily with fulltext queries only :-( All queries using fulltext indexes. I use huge mysql cofig (huge.cfg). What can I change in the mysql configuration for better performance? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MinGW and MySQL
Hello P I'm using QT4.0 which works with MinGW. What is the MinGW ? Database server? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing Up Database and All Tables
Hello See some of this: MySQL Snap 2.1 http://3d2f.com/programs/2-075-mysql-snap-download.shtml Backup Watcher for MySQL 1.8.3 http://3d2f.com/programs/4-919-backup-watcher-for-mysql-download.shtml MySQL Dump Timer 1.2.4 http://3d2f.com/programs/12-670-mysql-dump-timer-download.shtml MySQLBackup v4.3 http://3d2f.com/programs/27-397-mysqlbackup-download.shtml MySQL Auto Backup 1.40 http://3d2f.com/programs/29-537-mysql-auto-backup-download.shtml SP I am running mysql 4.0.15 on a Win2000 operating system. I SP would like to be able to back up the system with a perl script. I SP have a mix of InnoDB and MyISAM table types. Upon investigating SP this, I found this web site, with an article about backing up SP mysql databases: SP http://www.databasejournal.com/features/mysql/article.php/3421751 SP It looks like there is something called a InnoDB Hot Backup SP that is a perl script. Is this something that Mysql offers, or is SP it something one needs to purchase. SP My database is rather small, and I basically want to pull 2 SP backups a day, in case I have any hardware failures. SP If someone could help advise me on this topic, I would appeciate it. Sincerely, Michael, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 5.x Trigger Examples
Hello CJS I need a pratic examples of Triggers in Mysql 5.x Chapter 20. Triggers http://dev.mysql.com/doc/mysql/en/triggers.html 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing not empty selections
Hello, How to optimize != statement? SELECT * FROM table1 WHERE char_255_column != ''; How to fast select all rows, where CHAR(255) NOT NULL column not empty? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about backups, InnoDB tables, etc.
Hello JT Did you try that link? When I follow it, I get a search results page JT saying Search Results for backup Showing 1-30 of 53 found.. Too JT bad it doesn't actually show the search results Sorry. Try this links http://solutions.mysql.com/search.php?pc=4%2C86q=backuplevel=0 http://solutions.mysql.com/software/?c=backup 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query on a very big table
Hello CA The query takes ages to run (has been running for over 10 hours CA now). Is this normal? I think, your indexes are too complicated. MySQL have to rebuild it usually after ALTER TABLE. 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about backups, InnoDB tables, etc.
Hello RS I'm trying to get a handle on MySQL backups and hot backups using MyISAM RS and InnoDB tables together. We plan to switch from SQL Server to MySQL RS soon. Did you see it: http://solutions.mysql.com/search.php?pc=0%2C0q=backuplevel=0 ? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
random rows selection
Hello, How to select 5 random rows from big table with WHERE clause? This query very slow on 1 mln rows: SELECT col1, col2 FROM table WHERE col3=123 ORDER BY RAND() LIMIT 5 Have you a faster one? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random rows selection
Hello GP Similar questions have been asked before. For example: GP http://lists.mysql.com/mysql/184088 Thank you. I found something interesting: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id = @rand_id LIMIT 1; But this query can return empty results, if we use it with WHERE: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT col1, col2 FROM table WHERE id = @rand_id AND col3=123 LIMIT 5; :-( 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cant Get Access to My Databases
Hello I recommend you install mysql on your _home_ computer. After install last version MySQL-Front (www.mysql-front.com) and trying to connect from it. TH I have finally gotten my MySQL server up and running. However, I am TH having a bit of difficulty getting connected. TH I am using Dreamweaver to construct PHP pages for dynamic data, and TH phpMyAdmin to manage the databases, however when I try to connect by TH either method, I get a message that states 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
Hello BM Are you looking to compare a few last records in each table or ?? BM Can I get a little bit more details? I have 10 similar tables. 3 years ago it was one big table. Now I split it to 10 tables. 3 years ago I could write: SELECT * from BigTable WHERE date_col$some_date ORDER BY date_col Now I have to write: SELECT * from Table0 WHERE date_col$some_date UNION SELECT * from Table1 WHERE date_col$some_date ... UNION SELECT * from Table9 WHERE date_col$some_date after push this rows into temporary table and after write: SELECT * from TempTable ORDER BY date_col How to make it simply and faster? 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
Hello Now I use temporary table, but I can`t use SQL_CACHE in queries, which contain temporary table :-( In theory 90% queries can be cached. 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
Hello GP If you have faster solution (in several times) please introduce it. I've GP seen similar issues at archives at: GP http://lists.mysql.com/mysql I find it in docs :-) : (SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10) ORDER BY a; 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
