Before talking about MySQL performance tuning, it has to be clear which MySQL storage engine should be taken. I will talk about the two most popular storage engines: MyISAM and InnoDB.
I recommend at least basic knowledge of MySQL to understand most of the things explained in the following part.
At first it is important to mention that there is no storage engine which is best of all. Every storage engine has its advantages and disadvantages, but if you clarify your needs correctly you will find the one that fits the best.
In fact you do not have to use one storage engine for your whole database, but you are able to combine the advantages by using both of them for different tables. So probably the way to the best performance and usability is using MyISAM and InnoDB!
Description of the most important MySQL storage engines:
- Fields of use
This storage engine is used with simple and easy database models and purposes in general. For me the most important feature of MyISAM is full-text search.
It is the default storage engine of MySQL, because its simplicity is a great advantage. You do not have to take care of any complex database models and can easily create simple table designs.
Another important point is the full-text indexing, already mentioned as feature. You are able to use this for search engines in forums, websites or web-shops.
Due to the simplicity there are many things which MyISAM lacks, even if it is an advantage. MyISAM is missing of data integrity features such as foreign keys.
Also the missing transactions feature is bad, because queries can mess up your complete table content!
Another negative point is that this storage engine does table locking. This will surely reduce performance with WRITE-heavy tables.
MyISAM is the best storage engine for beginners or simple purposes, but I do not recommend using it with WRITE-heavy tables, due to table locking and the lack of data integrity and transactions.
All in all MyISAM is mostly considered to be used in relation with tables of websites, content management systems, blogs, small search engines and so on. As you can see, these examples are all READ-heavy and so it is mostly used in this field.
Of course this a general statement which depends on the size of the software or on specific tables. There are some cases on which other storage engines have better performance results with SELECT-statements, but this is not the standard.
- Fields of use
This storage engine is used with more complex database models and purposes in general. There are three main features which InnoDB has: Relational database design with foreign keys, transactions and row-level locking.
In my opinion the most important advantage of InnoDB is the transaction feature. With this you can ensure the correctness of your data, even if a data manipulation query gets interrupted.
Besides it supports row-level locking within tables, which is very important for the performance of WRITE-heavy tables.
Another advantage is the data integrity with the help of foreign keys. With this you can build relational database models, needed for complex use.
Moreover InnoDB not only keeps indexes, but also frequently accessed data in memory, what is possible with the buffer pool. Of course this reduces I/O on disks and due to this increases perfomance a lot!
In opposite to MyISAM this storage engine does not support full-text indexing, which prevents you from doing full-text searches.
Due to the fact that InnoDB maintains data integrity, designing database concepts will be more time-consuming.
Another thing is that it also needs much more system resources, especially RAM. Only with this it is possible to guarantee good performance.
To summarize InnoDB is a storage engine for advanced users with databases which contain sensitive data.
It is recommend to use it with WRITE-heavy databases, because of the transaction feature and data integrity.
Nowadays you can use InnoDB for READ-heavy databases too, because the times of slow performance are really gone. But this gets only important with huge applications or websites with huge count of visitors and changes.
I hope the differences and advantages of both MyISAM and InnoDB get clear after reading this article. If you have feedback, regards, corrections or questions please let me know and do not hesitate to comment!
Besides this was the first step to and the base of my MySQL performance tuning post, which will come soon.
Pingback: HowTo: MySQL Performance Tuning | Dev-Blog of DoesntMatter