What is the difference between mysql MyISAM and InnoDB? How to optimize the MYSQL database?

Source: Internet
Author: User
: What is the difference between MyISAM and InnoDB in mysql? How to optimize the MYSQL database ?, If you are interested in the PHP Tutorial, refer. Basic differences between MyISAM and InnoDB

1. InnoDB does not support FULLTEXT indexes.

2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

4. when deleting FROM table, InnoDB does not create a new table, but deletes a row. MyISAM is the table innodb is the row Lock

5. the load table from master (FROM the master load table) operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then import the data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. if MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "" 2%"

6. InnoDB supports transactions

Select a storage engine based on the actual situation.

In general, myIsam is recommended for queries.

If you need transaction processing or foreign keys, InnoDB may be a good method.

How to optimize the MYSQL database:

1. select the most suitable field attribute, minimize the length of the defined field, and set the field not null, for example, 'Province, gender'. it is best to set it to ENUM.

2. use join instead of subquery

3. use UNION instead of creating a temporary table manually

4. transaction processing (ensure data integrity. for example, if both are added and modified, both operations are performed, and both operations fail)

5. how to create an index? Advantages and disadvantages of indexes ?)

6. optimize SQL statements

7. the explain command shows the mysql execution plan.

8. table sharding (vertical table sharding, horizontal table sharding ?)




What is the difference between MyISAM and InnoDB in mysql? How to optimize the MYSQL database ?, Including some content, hope to be helpful to friends who are interested in PHP tutorials.

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.