MySql modifies the table type, mysql modifies the table

Source: Internet
Author: User

[Switch] MySql modifies the table type and mysql modifies the table

Recently, I encountered a problem about modifying the mysql table type. Previously, when phpmyadmin managed the mysql database, the created table is of the MyISAM type by default, and it is convenient to modify the table type. But now there is a problem with phpmyadmin configuration, and I am too lazy to do it. I installed another mysql management tool, which is more convenient. However, the table I created is of the InnoDB type by default, and this type is prone to problems during my previous migration. Although I cannot tell which type is better, I feel like MyISAM, I tried to convert it to the MyISAM type. This tool has not been found for a long time and I am not sure how to modify it. Fortunately, I found SQL commands on the Internet to modify sentences. It seems that SQL statements are powerful.

Modify an SQL statement of the mysql table Type:

Alter table name type = MyISAM;

Alter table name type = InnoDB;

 

Appendix mysql table type description

MyISAM: This is the default type. It is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. Compared with other storage engines, MyISAM provides most tools for checking and repairing tables. MyISAM tables can be compressed and can be searched in full text. They are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice.

InnoDB: This type is transaction-safe. It has the same features as the BDB type and supports foreign keys. The InnoDB table is fast and has more features than BDB. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. If your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations.

AUTOCOMMI is the main factor affecting the speed of the InnoDB type labels that support transactions. The default setting is open, and the program does not explicitly call BEGIN to start transactions, as a result, each inserted entry is automatically Commit, seriously affecting the speed. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if you open the autocommit statement), which greatly improves the performance.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.