The advantages and disadvantages of MySQL
MySQL is an Open Source, high performance, feature-rich relational database management software, it's been used in various environments. MySQL used to be the first choice for open source database system, sometimes the only choice, choose MySQL is a no-brainer. As technology advances we have much more choices now, depending on your project and use cases, MySQL may or may not the best choice. Selecting the right DB has become a problem of its own. Each decision has its consequences, especially the early ones, selecting DB is one of them. The way data is stored directly affects the way data can be used. It's always better to make crucial decisions like this consciously, i.e. to know what you are doing. There are many database products out there, for example the biggest competitor of MySQL, the PostgreSQL, which one is the best for you? To answer that question, you need to know the pros and cons and make the decision according your requirements. We will talk about the advantages and disadvantages of MySQL in this post.
MySQL is widely used
Although the accurate number of how many websites are using MySQL as database is impossible to figure out, but we can estimate that 80% of personal blogs are using MySQL database. Every LAMP or WAMP website already adopted MySQL. That will be a huge number.
The big companies like Google or Amazon don't use MySQL as their primary data storage, because MySQL can not handle the business situation in that scale. But for millions small or medium websites, MySQL usually the best choice.
Chose a widely used product give you a lot benefits.
Easy to interact with
When it comes to relational databases, we have a spectrum of choices. At the higher end are complex, industry level products like Oracle, Sql Server, they are feature rich and complex, need dedicated database administrators to operate, usually need professional training before get in the position.
At the lower end are those light weight products like SQLite or embeddable database like H2 database. They don't even need a standalone process. And they don't need any configuration.
MySQL sits in between. It's feature rich and don't need too many knowledge to use at the same time.
MySQL is easy to use, it just worked right out of the box. Most of the tasks can be done in the command line, most of the time you don't need a GUI. Because there are really not many complex things you need to do in MySQL, if you want to import and export data, the utility mysqldump is powerful and easy to use, you don't have to leave your command line shell. Often times, you only need to know some basic command lines, because the whole architecture of the database is really simple and compact.
Even a beginner can download, install and start to use it in less than 15 minutes.
If you need GUI, the web based administration tool like phpmyAdmin or adminer which are easy to get and use.
Thanks to its widely adopt rate and huge user base, a lot tools around it were developed.
It provides clients for almost all programming languages, the most used one maybe the PHP language. PHP has built in support API for interacting with MySQL.
With a MySQL manual and some basic SQL knowledges, you are good to go, you don't need a training course to be able to operate it.
To do Web development, you only need to select a LAMP stack from various choices, for free. LAMP stack is a mature and easy to use platform, even Facebook start from it.
MySQL has great performance
For web application, speed is critical, your user wouldn't wait your page to load, they just leave if your application don't responsive fast. The most common performance bottleneck is in database, select a high performance database is very important.
Performance is vital for any database management system, under so many years of development, you can trust the performance of MySQL. The core philosophy of the design of MySQL is make it quickly get data in and also quickly get it out, even it sacrifice some other important features, but if you are not very sensitive to those fancy features, the performance gain is worth it.
MySQL generally has better performance on simple queries we use everyday, such as primary key lookups, range queries, etc.
MySQL performs well as the data size grows, from GB to several TB of data. The newest storage engine InnoDB, now the default engine for new tables, has been re-architected to take the advantage of multi-core systems.
MySQL also provides query cache and main memory table to take advantage of today's hardware with large amount of memory resources.
MySQL is open source software
MySQL is under control of Oracle, a commercial database company, but its still open sourced, free to use software. Using open source software cost less money and more safe because everyone can access its source code , any problem can be fixed quickly.
Remember there is no one product that fits all, MySQL also has some disadvantages.
Stored procedure and trigger is limited
Compare to Postgresql, MySQL has a little choices when you need to write stored procedure and trigger. The stored procedure give you code reuse and encapsulation. But in MySQL, you don't get as many choices as in other RDBMS like Postgresql.
MySQL is hard to scale
MySQL is not designed to be scalable, even it's not impossible to make it scale, just like what Facebook did, but it takes some serious engineering effort to make it possible, usually you need a lot effort to make it work.
If you expect your application will grow to big size, you need to consider the decision of using MySQL as your database. It may works fine at the beginning, until some day you need to scale it. You may want to shard the data, that is, to distribute data from one table to multiple instances and machines, but MySQL don't support auto sharding, you need to maintains the nodes manually. What if you want auto failover? You need external utility or write your own script.
The difficulties of making MySQL scalable are driving people to switch to NoSQL database like MongoDB.
MySQL is not for large sized data
MySQL works fine in most small or medium applications, but when data size grows, the performance degrade. When the data grows, only the simple and indexed query get good performance, for a complicated query, it easily get slow sometimes even unable to fulfill the request in tolerable timeout. You need to carefully design your SQL query to make it still available.
I have a real example for you considering how MySQL performance would degrade when the data grows. I have this table called stat, it records every visit request to this website. It will record the IP, the user agent, request uri and browser refer and visit time. Here is the table structure
I need to constantly check the various kind of statistic information about the traffic, for example, how many visitors the site get in last 7 day? How many visitors comes from Google, in my case, 80% of traffic comes from Google, or more complex: select last 7 days and group by blog post and comes from Goole and sort descendant.
So I use the like query a lot, for example and refer like '%google%' or and UserAgent not like '%AhrefsBot%'. This kind of query can be a performance killer in MySQL.
The biggest performance hit happens when the number of rows in stat table exceed 5.0000+. A simple query like "select latest 1000 visitors comes from Google " may take 30s to finish. I have to clean the table periodically to make the statistical usable. Now I'm considering move the data to other databases like Mongodb, it sounds promising for this kind of logging like data.
MySQL is not fully compliance to standard SQL
MySQL is not fully compliance with SQL-92 standard, MySQL didn't support some standard features, and it has some extensions that don't belongs to standard SQL.
This may not be a serious problem for most small web applications. But it can be problematic when you have a big application and you need to migrate from MySQL to other databases.
MySQL is owned by Oracle
MySQL is an open source product but now it's acquired by Oracle who have total control of the software, many developers feels nervous about the situation. Some of them have turned to MariaDB.
When Oracle acquires Sun Microsystem, the MySQL which belongs to Sun was sold to Oracle too. Oracle database are mainly used in Enterprise and big corporations, it has an obvious dominance in this domain, but MySQL is still one of the competitors. Oracle published an official promise to keep MySQL competitive but the promise can expire.
Oracle may choose to weaken MySQL, that is, acquire to kill in the future. Or it can improve MySQL to make it better, treat it like it's own product. There are uncertainties out there.