12 optimized Mysql tips for small finishing _mysql

Avoid editing dump files

The dump file created by mysqldump is inherently harmless, but it's easy to try to edit. However, one should be aware that in any case an attempt to modify these documents proved to be dangerous. Visually altering these files can result in database corruption, resulting in system degradation. In order to keep your system from any trouble, you must avoid editing the MySQL dump file.

MyISAM Block Size

Most developers forget the fact that file systems often require a large myisam block to ensure efficient operation. Many developers do not know the block size setting. The myi file is stored in the Myisam_block_size settings, which can be used to modify large block sizes. The default value for the MyISAM block size is 1K, which is not the appropriate setting for most current systems. Therefore, developers should consider specifying a corresponding value.

Open Delay_key_write

To avoid database corruption when the system crashes Delay_key_write is closed by default. One might ask, if so, why put it in the first place? It is necessary to brush this file every time the database is written MyISAM key files. By opening it up, developers can save a lot of time. Refer to the official MySQL manual to find out how your version will turn it on.

Joins (table join)

Create an index and use the same column type: join (table join) operation can be optimized in MySQL. If there are many join operations in the application, you can optimize them by creating a join on the same column type. Creating an index is another way to accelerate your application. Query modifications help you retrieve the desired query results.

Optimize WHERE clause

Even if you only search a single line of MySQL will also query the entire table, so it is recommended that you only need one result when the limit set to 1. By doing this, you can avoid the system running through the entire table, so that you can find the records that match your needs as quickly as possible.

Using the Explain keyword on a select query

You definitely want to get some help with any particular query. Explain keywords are very helpful in this respect. It provides specific details of what you are looking for to find out. For example, you can get a lot of useful information by typing explain keywords before a complex join query.

Optimizing queries with query caching

The MySQL query cache is enabled by default. This is mainly because caching facilitates quick execution of queries, and caching can be used multiple times in the same query. You add the current date, currdate, and other PHP code before the keyword so that the query caches it to enable this feature.

Isolate bugs using stack traces

Various bugs can be isolated using stack_trace. A null pointer is enough to destroy a particular piece of code, and any developer knows it has the ability. Learn the details of using stack traces to avoid bugs in your code.

Set Sql_mode

Enumeration types are always very confusing. Since the field may have multiple possible values, including the one you specify and the null, there will be many problems with coding, and you will always get a warning that the code is incorrect. A simple solution is to set up Sql_mode.

Copy Code code as follows:

Start mysqld with
$–sql-mode= "Modes"
Or
$sql-mode= "Modes" (My.ini–windows/my.cnf–unix)
Change in runtime, separate multiple modes with a comma
$set [global| Session] sql_mode= ' modes '
Traditional is equivalent to the following modes:
Strict_trans_tables, Strict_all_tables, No_zero_in_date, Error_for_division_by_zero, and NO_AUTO_CREATE_USER

Modify Root Password

Modifying the root password is essential for some specific settings, and the following modifications are ordered:

Copy Code code as follows:

Straightforward MySQL 101
$mysqladmin-u root password [Type in selected password]
Changing users ROOT Password
$mysqladmin-U root-p [type old password] newpass [hit Enter and type new password. Press ENTER]
Use MySQL SQL command
$mysql-U root-p
Prompt "mysql>" pops up. Enter:
$use MySQL;
Enter user name want to change password for
$update user Set Password=password (Type new password here) where user = ' username ';
Don ' t forget the previous semicolon, now reload the settings for the users privileges
$flush privileges;
$quit

Backing up a database with the MySQL Dump command

Developers are aware of the importance of database backups, and can save lives when there are major failures in the system.

The simplest way to back up a database

Copy Code code as follows:

$mysqldump –user [user Name]–password=[password] [database name] > [dump file]

You can also use the shorthand "-u", "P" to replace "user" and "password" respectively.
Import multiple databases to a file just add the name of the database you want to export later:

Copy Code code as follows:

Mysqldump–user [user Name]–password=[password]
[The name of the database] [Second database name] > [dump file]

Many databases provide sequential backup functionality, and only need to add--all-databases parameters to back up all databases. If you don't like the command line, download automysqlbackup from SourceForge.

To adjust config configuration

Perl scripting MySQL Tuner is another powerful tool for optimizing database performance, which can help you make multiple adjustments and modifications to your MySQL configuration. You can visit the website of the project to learn more about it.