MySQL Optimization Setup Steps _mysql

If you are using the MySQL 5.0.x
The following content can be saved directly to replace the My.ini in MySQL, remember to modify the Basedir and datadir two columns of the path.

Copy Code code 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=2m
query_cache_size=64m
Query_cache_type=1
Table_cache=64
tmp_table_size=32m
Thread_cache_size=64
Myisam_sort_buffer_size=8m
key_buffer_size=256m
read_buffer_size=64k
read_rnd_buffer_size=256k
sort_buffer_size=208k
Skip-bdb
back_log=500
Skip-locking
Skip-innodb
Thread_concurrency=16
max_connect_errors=30000
wait_timeout=120
Max_allowed_packet=2m
interactive_timeout=120
Local-infile = 0

Increase database logging
At the bottom of the MySQL profile My.ini, add the following, remove the # annotation in front of the log type column that you want to log, and then = fill in the log file name (which needs to be manually established and written by the program) to make it effective.

Copy Code code as follows:

#Enter a name for the error log file. Otherwise a default name would be used.
#log-error=
#Enter a name for the query log file. Otherwise a default name would be used.
#log =
#Enter a name for the slow query log file. Otherwise a default name would be used.
#log-slow-queries= Log-slow-queries.txt
#Enter a name for the update log file. Otherwise a default name would be used.
#log-update=
#Enter a name for the binary log. Otherwise a default name would be used.
#log-bin=

Increase Chinese Full-text indexing
At the bottom of the MySQL profile My.ini, add the following content.

Copy Code code as follows:

# Minimum Word length to is indexed by the ' Full Text search index.
# You are might wish to decrease it if your need to search for shorter words.
# That's you need to rebuild your fulltext index, after you have
# Modified this value.
Ft_min_word_len = 1

The Full-text indexing feature is supported from MySQL4.0, but the default minimum index length for MySQL is 4. If the English default is more reasonable, but the majority of Chinese words are 2 characters, which leads to less than 4 words can not be indexed, full-text indexing function is not in name. Most of the domestic space vendors may not have noticed the problem, and have not modified the default settings for MySQL.

Why should I use Full-text indexing?

General database search are used SQL like statements, like statements can not use the index, each query is from the first traversal to the last one, query efficiency is extremely low. With general data above 100,000 or too many online, like queries can cause a database crash. This is why many programs provide only the reason for the title search, because if the search content, it is more slow, tens of thousands of data will not move.

MySQL Full-text indexing is specially designed to solve the problem of fuzzy query, the whole article can be indexed in advance according to the word, the search efficiency is high, can support millions data retrieval.

If you are using your own server, please set it right away and don't waste it.

If you are using a virtual host, please contact the space trader immediately to modify the configuration. First of all, MySQL's default value is a wrong setting for Chinese, and modifying the settings equals correcting the error. Second, this configuration modification is simple, a matter of minutes, and improved search efficiency also reduces the chances of the space business database being dropped. If you send this article to the space trader, I believe most will be willing to change.

pay special attention to: regardless of any of these modifications, you must restart the MySQL service so that the modifications take effect.
Restart method
Method: At start - Run Enter: net stop MySQL return , and then run input: net start MySQL return .
Method Two: or start - Control Panel - Administrative Tools - Services , and then find MySQL This one right click, select Restart Service .