Left to right: InnoDB or MyISAM, innodbmyisam

Left to right: InnoDB or MyISAM, innodbmyisam

Welcome to my original Website: http://www.phpthinking.com/archives/456

MyISAM is the default storage engine in MySQL. Generally, not many people care about this. It is a tricky thing to decide what storage engine to use, but let's take a look at it. Here we only consider MyISAM and InnoDB, because these two are the most common.

Next let's answer some questions:

• Does your database have a foreign key?
• Do you need transaction support?
• Do you need full-text indexing?
• What query mode do you often use?
• How big is your data?

Thinking about the above questions can help you find the right direction, but that is not absolute. If you need transaction processing or foreign keys, InnoDB may be a good method. If you need full-text indexing, MyISAM is usually a good choice because it is built in the system. However, we do not often test the 2 million rows of records. Therefore, even if it is slower, we can use Sphinx to obtain full-text indexes from InnoDB.

The size of data is an important factor that affects your choice of storage engines. Large-sized data sets tend to use InnoDB because they support transaction processing and fault recovery. The small size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. However, MyISAM may take several hours or even a few days to do these tasks. InnoDB only needs a few minutes.

The habit of operating database tables may also be a factor that has a great impact on performance. For example, COUNT () can be very fast in the MyISAM table, but it may be very painful in the InnoDB table. Primary Key query will be quite fast in InnoDB, but be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements are faster in MyISAM, but the updates is faster in InnoDB-especially when the concurrency is large.

So which one do you use? Based on experience, if it is a small application or project, MyISAM may be more suitable. Of course, using MyISAM in a large environment can also be very successful, but this is not always the case. If you plan to use a project with a large amount of data and require transaction processing or foreign key support, you should use InnoDB directly. But remember that InnoDB tables require more memory and storage. Converting a 100 GB MyISAM table to an InnoDB table may cause a bad experience.

How to set the storage engine in mysql? How can I change INNODB to MYISAM? How can I save databases, tables, and other objects in MYSQL in DOS?

1. How do I set the storage engine in mysql? ------------ The default value is myisam, which is also specified during table creation. For example, create table test (id int) engine = innodb;
2. How can I change INNODB to MYISAM? ------------------ Alter table test engine = myisam;
3. How to save databases, tables, and other objects in MYSQL in DOS? ----------------- Execute create database databasename; create table test (id int) in dos. In this way, the database and table are generated. The corresponding system file is under data in the mysql installation directory, the database name corresponds to a folder. For example, if you create database testdb, you can find the testdb directory under the data Directory. For objects such as tables, you need to check the specific engine. For myisam engine, there will be three files, test. frm, test. myi, test. there are three myd, and innodb only has one test. frm structure files, data and index files are all in the ibdata1 tablespace.
4. How does PHP connect to MYSQL? Do you have to enter the code? Is there any other simple method, such as UI-based setting -----------------. You need to write the connection information and I have found a php connection to mysql online. For details, refer
<? Php
$ Mysql_server_name = 'localhost'; // change it to your mysql database server.
$ Mysql_username = 'root'; // change the username of your mysql database.
$ Mysql_password = '000000'; // change the password of your mysql database.
$ Mysql_database = 'mydb'; // change the name of your mysql database.

$ Conn = mysql_connect ($ mysql_server_name, $ mysql_username, $ mysql_password, $ mysql_database); // explain down from the beginning of this sentence

$ SQL = 'insert into book (name, pwd) values ("ggg", "ggg ");';
// This is an SQL statement: Insert a record into the book table

Mysql_query ($ SQL );
// Execute the SQL statement

Mysql_select_db ($ mysql_database, $ conn); // select the database where the above table is located (this statement should be executed before the above sentence)

$ Result = mysql_query ($ SQL); // This sentence is completely redundant. It is the same as the one above!

Mysql_close ($ conn); // close the database connection
Echo "Hello! Operation successful! "; // Display prompt information

?>

When prestashop is installed, the Database Engine selects InnoDb or myisam. What is the difference between the two? What is better computing performance?

The performance of Myisam is better. The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.