- Much like MongoDB, the defaults sacrifice safety for performance, e.g. MyISAM tables (which everyone everywhere will tell you never to use, but are IIRC still the default) ignore things like foreign key constraints
- Lots of "error" conditions silently complete as best as they can, rather than failing with an error. E.g. trying to store the wrong type of value, or trying to make a table using a disabled storage engine, or trying to store a string in an encoding that can't represent all its characters. A lot of this is legacy that is slowly being improved on, but reputation naturally lags slightly behind
- The query planner is a bit simplistic, so if you want performance you do need to put a little more thought into tables so that you're not doing 5-way joins all the time
- Some more advanced "features" of other databases like triggers just don't exist. IMO they are bad features and shouldn't be used, but their absence provides an easy thing to criticize mysql for if you're that way inclined
It's perfectly possible to make a good product using MySQL, quite possibly better than you would with other databases. But you do need to be very scrupulous about checking for warnings after every statement.
Triggers have existed since 5.0.2 in MySQL.
The query planner has been greatly improved with MySQL 5.6 and the MariaDB fork.
PostgreSQL is a good SQL database.
And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects.
"Is the barrier to entry too low, and problems are from legions of rank amateurs?"
Maybe. Well, "low barrier to entry" is not a defect.
It seems to me that the success of MySQL can be tied directly to people using "database" for things where what they really need is "filesystem".
Great, you've just invented a half-assed DBMS.
My first thought is "Yeah, we already have one of those, it's called MySQL"
The success of MySQL can be tied directly to people using "database" for things where what they really need is "network filesystem".
The success of SQLite can be tied directly to people using "database" for things where what they really need is "filesystem".
Interesting that it's the same dynamic at play.
http://www.reddit.com/r/programming/comments/1niheb/mongodb_...
Why? I can't see one feature that MySQL delivers better than Pgsql...
PostgreSQL is a pain to setup, the SQL parser is very finnicky and for the past times I spent too much time trying to work around every idiosyncrasy of Postgres when I could have a similar MySQL instance running in a shorter time
So, MySQL doesn't waste my time with stupid crap.
Now please call me ignorant and downvote me since PostgreSQL fanboys can't take a critique and think their loved database is perfect.
However, one common use case (for me) is having to have a one-off install, which unfortunately kind of prevents that.
If my job needed frequently installs of DBs I would have probably gone this way, but it isn't, so today it's PostgreSQL 9.1 on Mac OS, tomorrow (more likely, a year or two from now) it's MySQL on Fedora, etc
My experience has been running on Slackware/RedHat/Ubuntu, though, and never on any Windows version, if that matters.
I really like how all values in the SQL in PostgreSQL can be replaced with arbitrary expressions, including stored functions. YMMV.
What is an hba file? How am I supposed to know what it does?
PostgreSQL has a "createuser" command. Great, but how am I supposed to know this is a PSQL command? (not to mention potential collision with other filenames)
RTFM? This is an acceptable answer, but it disregards completely the user experience.
MySQL is more intuitive. Authentication? User/password. None of this "ident" or "trust" (really?) schemes. Yes, pg_hba is more flexible, but if you have only one DB user... Yes, it's convenient to have a local "trust" connection. Also a little bit more insecure as well.
And there are convenient mysql* commands, you can tell they are mysql commands.
From the section in the docs titled "the pg_hba.conf file", which explain it quite clearly? [1]
> PostgreSQL has a "createuser" command. Great, but how am I supposed to know this is a PSQL command?
Its not a psql command, its a client application. Which you are expected to know by reading either the documentation section on client applications [2] or the documentation on the administrative function for which that particular application is used [3].
Of course, you don't need the program for the function, you can just use SQL. Which, presumably, you are going to be reading the documentation on for any RDBMS, since even the ones that support the standard the best (e.g., between PostgreSQL and MySQL, PostgreSQL by far) still have some implementation specifics you need to be aware of.
> MySQL is more intuitive. Authentication? User/password.
PostgreSQL supports that, and configures it by default in the project-supplied installers (IIRC, for local connections only by default.) You only need to configure authentication if you are using different options, or want to support remote logins.
> Yes, pg_hba is more flexible, but if you have only one DB user...
You don't need a multiuser RDBMS at all?
> And there are convenient mysql* commands, you can tell they are mysql commands.
And how do you know that they exist or what they do without reading the docs?
[1] http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.h...
[2] http://www.postgresql.org/docs/9.3/static/reference-client.h...
[3] http://www.postgresql.org/docs/9.3/static/database-roles.htm...
"(createuser) not a psql command, its a client application."
Yeah, so postgresql is the only program that creates users for itself? Createuser is a generic name.
(Some) Postgresql client appplications have no prefix. MySQL apps have.
So that if I see a file called mysqladmin I know it's from mysql. If I see a file called createuser I don't have that information. Who says it's from pgsql and not somebody else?
> And how do you know that they exist or what they do without reading the docs?
mysql <TAB>, then man or --help?
The CREATE USER issue is just bad faith on your part. It is a de-facto standard, used by Oracle, Transact-SQL and ironically MySQL.
In the end, your complain boils down to:
1. Having to edit pg_hba replacing "ident" with "password"
2. Unfamiliarity with pgsql opposed to familiarity with mysql
A developer that chooses tools primarily based on familiarity is on the slippery slope to dinosaur land. Not that it's bad -- Cobol development still pays well to this day.
"A developer that chooses tools primarily based on familiarity "
Yes, let me waste time to learn how to use the latest fad on HN, sure (not talking about PostgreSQL here)
And PostgreSQL is made of unicorn blood and my projects will be 200% better if I use it instead of MySQL, sure.
It's a DB. There's a narrow range where it would make a difference to use it instead of scaling to more servers, for example. And you can "always" convert later. (Unless you're FB apparently)
I will definitely grant you that mysql is easier to use than postgres if you're only willing to read the docs for one.
As for the parser being finnicky, that is a feature. Hint: an empty string in a timestamp does not mean midnight 1/1/1970. This is one area where mysql did more harm than good: getting developers hooked in sloppy SQL.
I've installed it on Linux and Windows and, no, its not. Its pretty much the same work as MySQL to setup, except the install usually wants information to create or use a system user for the DB server in the install, which is a trivial amount of extra work.
I understand this used to be an issue many years ago (IIRC, most of the streamlining of the setup happened in the first couple of major 8.x releases, in 2005-2006, about the same time as official native Windows support was added.)
> the SQL parser is very finnicky
How so?
In what way is it finicky?
For example, MySQL accepts both ' and " as string delimiters. It's very fun trying to get around PGSQL not understanding what you want to do while MySQL understands.
It doesn't require ';' to mark the end of a command
That is, in its default mode, MYSQL does not accept ANSI-standard quoted identifiers (supporting only non-standard use of backquotes for quoted identifiers), and instead treats double quotes as equivalent to single quotes for strings. (Of course, MYSQL has a non-default option to support the standard.)
PostgreSQL, OTOH, supports the standard out of the box, and treates double quoted material as quoted identifiers.
Neither behavior is more finnicky, one is just standards-compliant.
> It's very fun trying to get around PGSQL not understanding what you want to do while MySQL understands
This would appear to only be an issue if your only RDBMS experience is with MySQL and you are adapted to its quirks, since if you have experience with other DBs which adhere to the ANSI standards for basic behavior, MySQL will, by default, be more likely to not understand what you mean than PostgreSQL.
- Replace MySQL with MariaDB (hopefully your tables are InnoDB and the transition will be "relatively" seamless). You can continue using the rest of your app as-is.
- Yes. There are many by the simple fact that a lot of people are unaware of what they're really doing and what impact certain indexes, queries and even to some extent, compile-time options will introduce.
- Schema is everything for an RDBMS. If people just take the time to calmly and carefully consider what it is they're trying to do and what options they'll truly need vs. just guessing what future "growth" would be like, they will have fewer actual (rather than imagined) growing pains later.
Of course, most of these problems could be mitigated if new projects started off with Postgres or MariaDB, if they're taking the RDBMS route.
SQL works well, if you invest time understanding the theory and the limitations. If you care about the implementation you're going to use (whether it's MySQL, MS SQL, PostgreSQL or whatnot).
Unfortunately my rather sad experience is that people give a damn about it and will use the trial&error approach, until they cannot reproduce deadlocks/critical errors in their local 'runs on my Thinkpad' VM.
(I really need to quit)
Switching to PostgreSQL.
MS's graphical query plan tool is actually pretty awful and hasn't been updated since forever, quite often hiding the most important information, like estimated rows vs. actual rows. It takes quite a while to learn how to actually read it unless someone shows you. Like months. And it's really just a wrapper around SHOW PLAN, which MySQL has with EXPLAIN.
I'm not convinced that SQL Server Data Tools easily manages schemas, I've only been using it a few months but it seems a massive hack to me which relies on constant schema compares.
MS also constantly push you to use the graphical tools, but if you play in the tools and then try to make something repeatable it's a right PITA. Your only option is to turn on the incredibly annoying 'Autogenerate change scripts' whereas in MySQL you can just look at the history tab in Workbench which shows every command you've run. Imagine that. Everything as SQL scripts you can modify and re-run!
Finally, SQL Server Management studio is an absolute dog performance wise, I actually still miss Query Analyser which was much better IMO.
And you can't actually use SQL Server with something like Ruby or Python without quite a massive amount of effort. I know because I've tried, there's no decent, easy to install, equivalent of ADO.Net without mucking around with ODBC connections. At least a year ago there wasn't.
As for the problems with MySQL, he mentions in the post the problem with MySQL is the lack of certain types of operations.
Also there's a bunch of gotchas, sub-selects perform terribly in MySQL, which makes the EF unusable on it.
Still, I've used both and SQL Server is much better if you're developing with C#.
I don't know about a year ago, but several months ago I found pyodbc [1] and it's been wonderful. Easy to set up, works a lot like the mysql functions in PHP.
But really, Python + MSSQL = Bag of hurt.
It's a real pity, but I see the "market" for that being very small...
I wouldn't put too much stock in the graphical execution plans. Outside of the problem that it simply fails for complex queries, it lies about costings (costings are always based upon estimates, which means that when you have super-fast storage, as many new installs do, the query analyzer becomes a giant bag of lies), it can't "see into" table-defined functions (they sit there innocuously with negligible cost when often they sit in front of a curse of poor performance).
SQL Server is a great database, but it is very far from perfect. I mentioned above about the graphical execution plans lying about storage costs, and the same goes for the query planner -- it will choose horrific query plans under the assumption that you have one spinning disc of rust, falling apart when you're really on some FusionIO SLC. This can manifest in queries that run excellently one day falling completely to shit the next because it passed a threshold and has decided to switch join patterns. One would think that it would actively actually monitor storage/memory and CPU to make all of these weights, but it doesn't whatsoever.
The other major issue being lock escalation that can destroy SQL Server databases under heavy contention.
No one database is > 200GB so it's not "web scale" by any means but sometimes you need to pull out tricks like indexed/materialised views.
I use Transact-SQL directly via ADO in C++ and ADO .NET in C# and indirectly via EF and NHibernate.
It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans.
Quite frankly, I don't care that there are alternative RDBMS' or alternatives to traditional RDBMS.
But ... I keep hearing about problem after problem with MySQL. What's the trick to using it successfully? Is the barrier to entry too low, and problems are from legions of rank amateurs? i.e. is it as simple as constructing your schema thoughtfully, with tables that are "well-it's-almost-3NF" and making some educated guesses about which indexes might be needed in advance?