What is the difference between mySQL's MyISAM and InnoDB? How to optimize MYSQL database?

Basic differences between MyISAM and InnoDB

1. InnoDB does not support FULLTEXT type indexes.

2. InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM simply reads and saves it. A good number of lines is enough. Note that when the count(*) statement contains a where condition, the operations of the two tables are the same.

3. For fields of type AUTO_INCREMENT, InnoDB must contain an index with only this field, but in the MyISAM table, a joint index can be established with other fields.

4. When DELETE FROM table, InnoDB will not re-create the table, but will delete it row by row. MyISAM is the table that innodb has row locks

5. The LOAD TABLE FROM MASTER (from the main load table) operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, and then change it after importing the data. into an InnoDB table, but does not apply 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 range to be scanned when executing a 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 things

Select the storage engine according to the actual situation

Generally, it is recommended to use myIsam if you have multiple queries.

If you need transaction processing or Foreign keys, then InnoDB may be a better way.

Methods to optimize MYSQL database:

1. Select the most applicable field attributes, reduce the length of the defined field as much as possible, and try to set the field to NOT NULL, for example. 'Province, Gender', it is best to set it to ENUM

2, use join instead of subquery

3, use union (UNION) instead of manually created temporary table

4, transaction processing (guaranteed Data integrity, such as adding and modifying at the same time, if both are true, both will be executed, if one fails, both will fail)

5, Proper indexing (how to create an index? What are the pros and cons of indexing?)

6, Optimize sql statement

7, explain you can see the mysql execution plan

8, sub-table (vertical sub-table, horizontal sub-table?)

Related reading

http://www. phpernote.com/mysql/500.html

http://www.nowamagic.net/librarys/veda/detail/1899

The above introduces the difference between MyISAM and InnoDB of mysql? How to optimize MYSQL database? , including relevant content, I hope it will be helpful to friends who are interested in PHP tutorials.