Why MySQL / MariaDB ?
MySQL is the most famous RDBMS. It's available under the GNU General Public License. Despite its great accessibility, there are many subtleties in the different versions and alternative distributions (Percona, MariaDB) of MySQL. Good MySQL usage implies taking some time to ensure its configuration suits the usage otherwise it's very likely to bite at some point. Also, while MySQL popularity success comes mainly from open source, it's mostly its owner Oracle who actively develops and maintains it.
Use cases in short:
- Web applications / sites
- OnLine Transaction Processing
- Tabular data storage and analytic
- Cloud providers availability
- Multi-master replication
A lightweight database for the web
In its earlier versions (<8.0) MySQL focuses on faster performance for web applications and web-driven websites, front-ends. For a CMS MySQL will be used to store user defined data and retrieve it through indexed queries. A good approach for these use cases is to concentrate on a simple database design optimized for reading and processing single source of data. Normalized data shouldn't result in too many joins (<5). An other aspect pushing MySQL on the web is that buying a license is necessary when distributing it bundled inside a desktop app while it is free for web usage.
MySQL is great for OLTP, fast transaction processing to support live interactions with users resulting in a lot of predictable concurrent queries. It means everywhere you need to maximize the number of transactions per second: ecommerce, shopping carts, ticketing, reservations, online payment... That's because MySQL performs better than other databases for huge number of connections. It fits applications that open many connections or have to deal with a lot concurrent users. MySQL is also fast for updates and small concurrent inserts, selects, deletes. Ideally there is a finite set of queries that the application can make. Because queries are all known in advance they should be well optimized and principal concern is the volume of queries you get.
Additionally MySQL can manage "Excel dimension" data storage and analytic well. For applications filling this particular role where data is tabular, users use tables in the front-end, MySQL is a safe pick. It doesn't mean that stored data has to be a replica of a spreadsheet and that normalization isn't to be done.
MySQL in the cloud and version limitations
MySQL is almost available on every cloud hosting. MySQL Database As A Service exists for the biggest: Amazon, Azure and GCP, for the challengers like Digital Ocean, OVH and for a lot of the smallest companies. For small providers you might very well stuck with it, as MySQL can be the only database they have. It can make for a cheaper database hosting, but it's rarely the last MySQL version. Be cautious, look after version constraints. As a quick example, MySQL is not configured by default to use utf-8. Older MySQL versions (5.X series is common) have a noticeable limitation on the VARCHAR index size of 255 characters and ignore the exceeding ones. Some advanced SQL functionalities are not available on MySQL. It's recommended that you test all the expressions to be run against your database server to check whether MySQL match your particular set of queries before settling.
MySQL benefits from a broad tooling. Clients, connectors, orm, applications, open source projects and documentation ensure that you can find something to work with. MySQL has proven replication and load balancing solutions: mature, well-understood and documented. With the current database offering, it's becoming less of an advantage because those features are more frequent. It's especially the case when choosing to go with cloud services which can provide replication and load balancing for a lot of databases. Still, for self administration and general accessibility it does matter. Proxies options are proxysql, haproxy. MySQL can bring multi-master replication to your stack with either Galera, Percona XtraDB Cluster or MySQL InnoDB Cluster. It can rely on multiple storage engine. However, its bread and butter ACID compliant storage engine is InnoDB. Alternative storage engines depends on distribution. Certain features require a particular engine. That's the most common reason to move out of InnoDB.
Finally, a note on MariaDB, why MariaDB ? It has the advantages of an open source project. It integrates MySQL with a bunch of other good open source projects. An important fact is that it doesn't move as fast as Oracle MySQL. It gains from it a progressive integration of new but already proven features. For now MariaDB keeps standing as a drop in replacement to Oracle MySQL. More generally choosing a MySQL distribution involves planning in advance which distribution specific features are to be used passed the common ones. It can also be deciding on which company will provide you a paid support or on which cloud your database will be hosted.
With MySQL 8.0, Percona server for MySQL 8.0, MariaDB Column Store, MySQL can now serve to store vast amounts of data and handle analytic charges effectively beyond being a transactional application database. These added capabilities, and the new JSON document store with the X Protocol show that the team behind MySQL is capable to innovate to stay on top. A side effect is that MySQL is becoming a heavier database system.