I faced this dilemma when trying to improve the performance and speed of a particular site with more than 10,000 daily visits and relational databases with more than 200,000 records. An IDEAL solution is proper planning to avoid unnecessary queries or having the database execution load borne directly by the client.
MySQL
MySQL is an open-source system that allows information to be stored on computer systems, which, in turn, allows the choice of engines such as InnoDB and MyISAM, among other less relevant ones.
Aside from the tuning that can be done to the MySQL configuration (my.cnf, 50-server.cnf, etc.), to improve cache performance, CPU usage, RAM usage, or buffering on a VPS, you can configure the assignment of two engines per table: InnoDB and MyISAM.
MyISAM
MyISAM, in my opinion, is more popular due to its simplicity. It's a simple engine that allows for greater ease when managing a database. It can be optimized relatively easily. MyISAM consumes less RAM compared to InnoDB, and is recommended for a server with limited resources or for small databases (a store with fewer than 2,000 products or with few visitors, for example).
I've generally used MyISAM because it's the default engine for table creation and is versatile. However, when you're dealing with a large volume of data accompanied by high traffic, simply tuning the cache isn't enough.
InnoDB
InnoDB is a prevalent storage system that has established itself due to its good performance and reliability. Currently, MySQL versions 5.6 and above are the default engine for table generation in MySQL (in personal research, I've found that classic CMSs like Prestashop, starting with version 1.7, have adopted this engine as their default, but this is not the case with WordPress).
InnoDB tables organize data under a primary key, slowing down data updates (UPDATEs) and insertions (INSERTs). However, it is faster for selection in large databases.
Conclusions
It's necessary to understand the different types of database engines. When choosing one, it shouldn't be based on personal preferences, but rather on the suitability for each type of project generated from data analysis (or the resulting problems).
MyISAM should be faster at inserting, updating, and deleting records, but it has lower performance in high-volume relational databases.
InnoDB is also a fast engine, notable for its inclusion of row-level (not table-level) locking, transaction-level locking, and relational table design. This is especially true when you have tables that are executed too frequently. Under normal conditions, table-level locking should be sufficient.