Mr. Puneet Kishor wrote: > On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: >> MySQL should be avoided like the plague. > > why? > > This is a long standing (un)conventional wisdom to which I too have hewed. > Now, it so happens, I will be starting work on a project that uses MySQL > exclusively, and has done so for years. They have been very happy with it. > And, even though I feel like telling them that they should move to Pg, I > don't really know what the reasons are. I am not sure if all the reasons that > might be, are still valid. > > Of course, I don't want this to become a long, religious threat that might be > inappropriate for this list, or bore most of the other readers to tears. But, > it merits asking, why should MySQL be avoided like the plague? It is a strong > statement that requires backing evidence, else it would be construed FUD.
Perhaps my statement was a bit strong, so I will clarify a bit. *And* I'll give concrete examples. 1. Firstly, the context for my statement is someone who is not currently using MySQL, and so they don't already have an investment in it and codebase designed for it. If one is already using MySQL, then that is the status quo and the question is on whether benefits from a change to something else is worth the effort or not. But if one is not already using it, and their current DBMS can't be used how they need, then they have to change anyway and the question is between whether to move to MySQL or to something else instead; I am addressing this latter situation, and you'll notice I also said sticking with SQLite is even better if its usage can be fixed. 2. I consider MySQL to be an 80% solution. It does the job for which it is used adequately in many cases, and it is successfully used in many places, including to drive many businesses and organizations for mission-critical purposes. At the same time, MySQL has a lot of severe flaws, including bugs, mis-features, and missing useful features. I won't go into too many details on this here because a lot has been written on the subject already that you can reach with Google, although I will give some examples. So, you could do much worse than MySQL, but you could also do much better. 3. I have many years of personal experience with SQL DBMSs both large and small, including many years in using MySQL in production at multiple sites; my current main job uses MySQL in fact, so I'm using it day in and out today. I have personally found numerous ways in that MySQL lets me down and I have to work around it, where in my usage of Postgres it has not let me down. Here are a few *current* examples that I discovered (I had previously known of many more) because they bit me personally in the last few months (using MySQL 5.0, though from my reading these are unfixed in the latest versions): a. MySQL silently ignores all CHECK constraints in all engines, so for example you can't even tell it you want a column to only hold values between 1 and 10. Its in the MySQL docs: "The CHECK clause is parsed but ignored by all storage engines." b. That's just an example of how MySQL silently ignores lots of errors or silently changes data on you, such as silently truncating text values that are too long for a field when you insert them, so you've lost data without even knowing it. (Okay, I knew about this one previously.) c. MySQL treats all subqueries in the WHERE clause as being "dependent subquery" even if they are in fact "independent" (have no free variables to be filled in by the outer query), so they reexecute the inner query for every row in the outer, instead of running the inner just once. This is a severe performance drain, and so an example query that took 1 second if reformatted as a FROM subquery plus join would take over 10 minutes (before I killed it) as a "IN" subquery. And this is on tables that are all properly indexed. The WHERE version is much more concise code than the alternative, which is 2-3X as verbose. d. MySQL seems incapable of using indexes on derived tables to make them faster, not automatically nor provides a way to manually specify the use of such. So we use a bunch of explicit temporary tables with explicit indexes. e. All MySQL versions have a serious limitation where you can't refer to the same temporary table more than once in the same statement or stored function. See http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html . So then you can't use temporary tables either to refactor common parts of a query. f. My understanding is that many MySQL constraints only affect data manipulation done after they were defined; adding constraints to a table won't catch bad data already in the tables; I haven't personally verified this one. And those are just the tip of the iceberg. See Google. Or MySQL's own manual, which spells out many of its deficiencies. I say avoid MySQL like the plague because it will bite you in so many ways, while an alternative like Postgres will only bite you in relatively few and less painful ways. Postgres is more of a 90-95% solution relative to MySQL's 80%, assuming that there is no 100% solution. I also know quite a number of savvy people in the developer communities who have used both Postgres and MySQL, and a vast majority of those prefer Postgres and strongly recommend it over MySQL when one has a choice, and so do I. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
