MyISAM or InnoDB? Engines in MySQL
Know differences between these two table engines in MySQL
In the next article I will try to describe the differences of MySQL with MyISAM engine and InnoDB. I have spent some time trying to find information that is relevant when making decisions that can directly influence the performance of a web system and I think I can briefly condense some differences that allow the developer to make the choice easier.
I faced this dilemma when I was trying to improve the performance and speed of a particular site with over 10,000 daily visits and relational databases of over 200,000 records. An IDEAL solution is adequate planning to avoid unnecessary queries or that the load of execution of the databases is assumed by the client directly.
mysql
MySQL is an open source system that allows the storage of information in computer systems which, in turn, allows the choice of engines such as InnoDB and MyISAM, among others that are less relevant.Outside of the tuning that can be done to the MySQL configuration (my.cnf, 50-server.cnf, ...), for improvements in cache performance, CPU usage, RAM usage or buffering in a VPS. The allocation of two engines can be configured per table: InnoDB and MyISAM.
MyISAM
MyISAM, in my opinion, is more popular due to its simplicity, it is a simple engine that allows greater ease when managing a database. It can be optimized relatively easily. MyISAM consumes less RAM memory compared to InnoDB, recommended for a server with limited resources or for small databases (a store with less than 2,000 products or with few visits, for example).I have generally used MyISAM because it is the default table creation engine, as well as being versatile. However, when faced with a large volume of data accompanied by high traffic, it is not enough to just tune the cache.
InnoDB
InnoDB is a popular and established storage system due to its good performance and reliability. Nowadays, on versions 5.6 of MySQL it is the default engine for generating tables in MySQL (in personal research, I have found that classic CMS like Prestashop since version 1.7 have assumed this engine as default, not yet with Wordpress) .Tables under InnoDB organize data under a primary key, slowing down your update (UPDATE) and insertion (INSERT) of data. However, it is faster in selection for large databases.
Conclusions
It is necessary to know different types of database engines. At the time of the choice, it should not be based on personal preferences, but on the adaptation to each type of project generated from a data analysis (or the emergence of problems, as a consequence).MyISAM should be faster when it comes to inserting, updating, and deleting records, but it has lower performance in large relational databases.
InnoDB is also a fast engine, highlighting the incorporation of row-level (and not table) locking, transactions, and relational table design. Standing out when you have tables that get executed too much. Under normal conditions, a table-level lock should suffice.
- Compártelo con tus amigos
.svg){kind=logo}
