MySQL optimization settings

If MySQL 5.0.x is used
You can directly Save the following content and replace my. ini in MySQL. Remember to modify the path of the basedir and datadir columns.

CopyCode The Code is as follows: [client]
Port = 3306
[MySQL]
Default-character-set = GBK
[Mysqld]
Port = 3306
Basedir = "D:/web/MySQL /"
Datadir = "D:/web/MySQL/data /"
Default-character-set = GBK
Default-storage-engine = MyISAM
Max_connections = 1910
Query_cache_limit = 2 m
Query_cache_size = 64 m
Query_cache_type = 1
Table_cache = 64
Tmp_table_size = 32 m
Thread_cache_size = 64
Myisam_sort_buffer_size = 8 m
Key_buffer_size = 256 m
Read_buffer_size = 64 K
Read_rnd_buffer_size = 256 K
Sort_buffer_size = 208 K
Skip-bdb
Back_log = 500
Skip-locking
Skip-InnoDB
Thread_concurrency = 16
Max_connect_errorrs = 30000
Wait_timeout = 120
Max_allowed_packet = 2 m
Interactive_timeout = 120
Local-infile = 0

Add database log records
In the MySQL configuration file my. add the following content at the bottom of INI to remove the # annotator before the log type column you want to record, and then enter the name of the log file (this file must be created manually, Program can be used to write logs to the program. copy Code the code is as follows: # enter a name for the error log file. otherwise a default name will be used.
# log-error =
# enter a name for the query log file. otherwise a default name will be used.
# log =
# enter a name for the slow query log file. otherwise a default name will be used.
# log-Slow-queries = log-slow-queries.txt
# enter a name for the update log file. otherwise a default name will be used.
# log-update =
# enter a name for the binary log. otherwise a default name will be used.
# log-bin =

Add Chinese full-text index
Add the following content at the bottom of MySQL configuration file my. ini.Copy codeThe Code is as follows: # Minimum word length to be indexed by the full text search index.
# You might wish to decrease it if you need to search for shorter words.
# Note that you need to rebuild your Fulltext index, after you have
# Modified this value.
Ft_min_word_len = 1

Full-text indexing is supported since mysql4.0, but the default minimum index length of MySQL is 4. If the default value of English is reasonable, but most Chinese words are 2 characters, the words with less than 4 words cannot be indexed, and the full-text index function is also false. Most domestic space vendors may not have noticed this problem and have not modified the default settings of MySQL.

Why should we use full-text indexes?

In general, the like Statement of SQL is used for database search, and the like statement cannot use indexes. Each query goes from the first to the last one, and the query efficiency is extremely low. Generally, if the data exceeds 0.1 million or the number of online users is too large, like queries may cause database crashes. This is why many programs only provide title search, because if the content is searched, it will be slower and tens of thousands of data will not run.

MySQL full-text index is provided to Solve Fuzzy queries.ArticlePre-indexing by words, high search efficiency, and support million-level data retrieval.

If you are using your own server, set it immediately. Do not waste this function.

If you are using a virtual host, please immediately contact the Space Provider to modify the configuration. First, this default value of MySQL is an incorrect setting for Chinese characters. The modification is equal to correct the error. Second, the configuration modification is very simple, that is, a few minutes, and the improved search efficiency also reduces the chance of the Space Provider database downtime. If you send this article to a space provider, I believe most of them will be willing to change.

Note: after any of the preceding modifications, you must restart the MySQL service to make the modifications take effect.
Restart Method
Method 1:Start-RunInput:Net stop MySQLEnterAnd then run the following input:Net start MySQL Enter.
Method 2: orStart-Control Panel-Management tools-ServiceAnd then findMySQLRight-click this item and selectRestart service.