in MySQL data table, use MyISAM engine faster, storage space is smaller; InnoDB engine insertion speed is slightly faster, the key is to support transactions.
Is it good to use MyISAM or InnoDB in practical applications? Specific problem specific analysis, determined by your system characteristics,
If the use of MyISAM, get performance improvement, but will lose the support of the transaction, the value is not worth it, but also to fully understand the pros and cons of the two ways,
Here is one of my test results:
MyISAM |
Data volume |
Data occupy space (byte) |
Index occupied Space (byte) |
Query based on index (s) |
No index query (s) |
Coun (*) query (s) |
Insert 1 million record (s) |
Insert 10 million record (s) |
5 million |
985,661,796 |
43,043,840 |
0.001 |
1.766 |
0 |
33.873 |
|
10 million |
2,350,000,001 |
234,428,417 |
0.002 |
3.599 |
0 |
38.236 |
|
55 million |
12,925,000,000 |
1,449,609,216 |
0.002 |
19.785 |
0 |
|
380.007 |
100 million |
23,500,000,000 |
2,664,473,600 |
0.002 |
35.797 |
0 |
|
401.562 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
InnoDB |
Data volume |
Data occupy space (byte) |
Index occupied Space (byte) |
Query based on index (s) |
No index query (s) |
Coun (*) query (s) |
Insert 1 million record (s) |
Insert 10 million record (s) |
5 million |
2,271,215,616 |
361,758,720 |
0.001 |
4.385 |
2 |
16.771 |
|
10 million |
3,295,674,368 |
524,288,000 |
0.001 |
8.277 |
3 |
21.794 |
|
50 million |
17,087,594,496 |
2,710,568,960 |
0.002 |
43.749 |
10 |
|
233.402 |
100 million |
35,186,016,256 |
5,579,472,896 |
0.001 |
174.403 |
21st |
|
233.162 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Conclusion: Comparison of MyISAM and InnoDB test results (average) |
|
|
|
1.517853328 |
Space: InnoDB is 1.5 times times the size of MyISAM |
|
|
|
1 |
Based on index query: the difference between the two is very fast |
|
|
|
3.78712652 |
No index queries take time: InnoDB is 3.8 times times the size of MyISAM |
|
|
|
1.690019777 |
Insertion Speed Time: MyISAM is 1.7 times times of InnoDB |
|
|
|
0 |
Execute COUNT (*): MyISAM hardly need time, InnoDB tens need 3-20 seconds |
|
|
|
MySQL based on MyISAM engine and InnoDB engine performance test comparison