21 ms·
MySQL is to SQL like MongoDB to NoSQL
- xd 13y agohttp://www.xaprb.com/blog/2013/10/01/mysql-isnt-limited-to-nested-loop-joins/ http://www.xaprb.com/blog/2013/10/01/mysql-isnt-limited-to-n...
- JunkDNA 13y agoI note for those who don't get to the bottom, there are comments there that suggest the author of the post you linked is incorrect. I'm not qualified to know who is right.
- xd 13y agoBoth comments are speculative. Are people, in general, aware that MySQL employs a pluggable storage engine? I wonder if much of the confusion about MySQLs abilities stems from arguments from people aware of it against those that have only ever used the stock engines.
- debacle 13y agoMost people use the default storage engine, without really thinking about it, but almost every project I've worked with recommends innodb - not sure why.
- VLM 13y agoThere's no surprise it became the default storage engine instead of myisam around early ver 5.5 or so, it is superior to myisam in many ways. Personally I like the row level locks and the way it can enforce foreign keys, which myisam can't do.
- taspeotis 13y agoAt work we have an MS SQL Server instance managing ~1TB of data spread among various databases. 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?
- eksith 13y agoAnswer to your last 3 questions : - 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.
- Keyframe 13y agoI keep hearing people recommend MariaDB, which is fine. I'd add in Percona as well. I have/had a decent run with it, but MySQL limitations with querying make me look towards Postgres. SQL engines are hard to do right, no wonder most of MPPs are built on top of Postgres.
- raverbashing 13y ago
- leif 13y agoI may be biased because I work on it, but I think TokuMX solves or will soon solve all of the "big data scaling" problems that exist in MongoDB. There is definitely still room for polyglot strategies: Zookeeper is for tiny but crucially consistent data, Riak is for fancy distributed systems availability guarantees when you can afford a simple data model, and I believe Redis has value as a sophisticated programming model for things you can fit in RAM (but I actually have no Redis experience personally). But in the past few months, I've grown to be really impressed with the document model, and the aggregation framework is getting more and more powerful with each release. I think that's the important part of MongoDB (sharding's a little messy and Riak seems to have their heads on straighter with that), and TokuMX takes that and sands off the rough edges you see with big data sets and concurrency, and I think that's going to end up dominating MongoDB and being a really compelling point in the NoSQL space.
- leokun 13y agoPeople like to give MongoDB shit, but is actually pretty fun to use. I wouldn't use it as a "big data platform." At scale I'd use Cassandra. For relational data I'd use postgresql. For memory caching, Redis. So when do I use MongoDB? For prototyping. Why? Because it's fun to use.
- threeseed 13y agoSorry but "fun" or "developer productivity" aren't allowed. You have to have a database that is expensive, obtuse or difficult to manage to be taken seriously.
- AlisdairO 13y agoTo be taken seriously when making performance claims about 'relational databases' rather than 'mysql', yes, you may need to venture into products that aren't as simple as mysql.
- alrs 13y ago"Developer Productivity" that leads to "ops people refuse to work here" isn't a stellar strategy.
- threeseed 13y agoYou would think someone who claims to be an expert on databases would have a clue about his industry. MongoDB is a document database and is as different from almost every other NoSQL database as it is from every SQL database. It very much stands alone and requires your domain model to be structured in a particular way. To say that it "represents" NoSQL is ridiculous. And to act like a guide on how to scale MongoDB to store 100GB is a problem is also ridiculous. I can create domain models that almost every SQL database would struggle with that MongoDB could breeze through and vice versa. And seriously anyone claims Cassandra or Riak are misspent adventures are simply delusional. They are solving real world problems in particular around horizontal scaling today that could never be done as cheaply or easily as before. A master-master cluster that costs nothing, scales linearly and can be managed by a developer. Would love to know what product existed years ago that could do that. Likewise I take exception with the criticism of MySQL. It is an easy to use, manage and install and has the best tooling bar none. It does what it is intended to do perfectly. Some people who deal exclusively with ORM layers will never see the imperfections and just see the ease of use.
- taspeotis 13y ago> I can create domain models that almost every SQL database would struggle with that MongoDB could breeze through and vice versa. I am always interested in learning about models that SQL/"traditional relation model" can't easily (or less easily) represent or query. Last time I asked someone pointed me to Datomic's EAVT (entity-attribute-value-time) model as a good example. Do you know of any more than EAVT?
- mcphilip 13y agoOne classic example is tree/graph structured data. I've worked extensively with modeling and querying medical concepts and relationships in RDBMS. I realize there are tools like recursive common table expressions an materialized paths that can aide querying such data, but now that I'm working at a different job using neo4j, I can see how much simpler the medical informatics domain could be modeled and traversed in a graph database.
- hrvbr 13y ago
- TazeTSchnitzel 13y agoMySQL is also generally poorly "designed". MySQL is to a database as PHP is to a programming language.
- RobAley 13y ago> MySQL is to a database as PHP is to a programming language. Yup. They're both easy to work with, highly productive, widely supported and tooled, and suitable for a very large number of projects.
- jhh 13y agoI feel that both your remark and the one you were reacting to are strangely correct and do not contradict each other. MySQL does "simply" work for many projects, especially if the number of rows doesn't go much higher than a few million. Similarily, while the design of PHP must be considered terrible in some respects, it certainly allows you to quickly create webapps and ist does so in a way that is rather "native to the web". But I think if you start non-trivial new projects on the PHP+MySQL stack today you are just lazy. Python (for example) with modern frameworks and Postgres gives you virtually all the advantages with fewer of the downsides and many nice extras like a more vibrant higher quality ecosystem and a better designed language.
- rimantas 13y agoEven trivial projects become non-trivial when they reach the scale of Wikipedia or Facebook. I am a bit surprised that nobody mentioned MySQL lacking transacion support in this thread, because otherwise a lot of comments sound "I don't really try to use/learn it, but I heard it is a toy db, and PG is real DB". Well, ok.
- MrBuddyCasino 13y agoMySQL lacks transaction support? Unless you use MyISAM tables, that isn't true since like forever. For the record, I prefer Postgres over MySQL any day.
- zamalek 13y agoWhile a lot of people people have disagreed with this guy in the past, I find it very hard to disagree with him in most of his posts. My exposure, as far as SQL goes, has been MsSQL, PostgreSQL and MySQL. With absolutely loads of MsSQL, and pretty hairy data structures at that (they have an aversion to the polyglot approach where I work) - SQL is one of the most enjoyable things that you can do once you get past the elementary "SELECT WHERE" (and stop using graphical development tools). I have been saying what Markus said for a long time, MySQL is why RDBMS has a bad name and why the "not using SQL" movement exists. I really hope the MariaDB team spend their time where it is needed most (a join engine that doesn't suck), if they haven't already. I mean, at the end of the day you have MySQL that can't even do hash joins, and then you have PostgreSQL with GEQO: http://www.postgresql.org/docs/9.0/static/geqo-pg-intro.html http://www.postgresql.org/docs/9.0/static/geqo-pg-intro.html
- xd 13y agoYou should know that the fork of MySQL, MariaDB, addressed hash joins well over a year ago: https://mariadb.com/kb/en/block-based-join-algorithms/#block-hash-join https://mariadb.com/kb/en/block-based-join-algorithms/#block...
- zamalek 13y agoAwesome! It really sounds like they are making a decent effort. I did some more searches and they even support things like join elimination[1]. [1]: https://mariadb.com/kb/en/what-is-table-elimination/ https://mariadb.com/kb/en/what-is-table-elimination/
- xd 13y agoThey're doing all kinds of awesome and the greater community is starting to see this, which is evident with Archlinux* for example whom has dropped MySQL in favour of MariaDB. Slowly but surely MySQL will fall to the waysides, which I can't help but think is what Oracle wants. * https://www.archlinux.org/news/mariadb-replaces-mysql-in-repositories/ https://www.archlinux.org/news/mariadb-replaces-mysql-in-rep...
- jsemrau 13y agoNo it's not . Mongo sucks. MYSQL is a great tool.
- cnlwsu 13y agoNot even Apple/Google threads have this much fanboisms and useless comments.
- pmelendez 13y ago> "In my eyes, MySQL has done great harm to SQL because many of the problems people associate with SQL are in fact just MySQL problems" That's a very strong and subjective statement. If any, resources heavy databases like Oracle or PostgreSQL, had brought more users to NoSQL than MySQL. That's doesn't there is something wrong with those databases, only that they weren't the right tool for the job is some cases. Also "MySQL problems" are most of the time due to poor usage, not to the database system itself, actually when used properly MySQL/MyISAM is a great tool
- TylerE 13y agoThat is pure FUD. Postgres is no more resource heavy than MySQL (the stock config will only use 32MB of ram!), and will perform better on many real world workloads.
- lmm 13y agoI think it's not resource heaviness per se so much as high latency (and low user-friendliness) in a dev configuration. Postgres takes noticeable time to start up, both server and client, and the client feels less responsive; its commands are also more arcane (e.g. mysql's "show tables" is something like "\d"). Postgres is quite possibly better for a "production" configuration, but it's much slower to develop with, so developers get to thinking of it as "slow".
- TylerE 13y agoI've never noticed latency issues. Edit: Timing Running the client: 12ms Server Startup: <400ms (due to the way OS X services work hard to time directly) Server Shutdown: 925ms Those are on a 3 year old Mac Mini - hardly a stud machine. Why are you using the command line client anyway? You know pg_admin exists and is free and runs everywhere, right?
- mattkrea 13y agoWhy is it that when it comes to databases no one is unbiased? I cannot find any reliable articles on the web concerning database engines that I look at and trust the author. It's even more difficult considering I am far from a pro with databases but quite simply I won't use any MS products and so I've used MySQL for smaller projects. I've started using MongoDB a lot more lately and regardless of what people (mostly people who've never used it I would imagine) say about it I love it.
- sergiosgc 13y agoIf the article assertion is true, then what is the Postgresql of NoSQL? In OSS RDBMSs, Postgresql quickly rose to the position of best designed, less quirky contender (albeit waay slower than MySQL in the past). Is there a NoSQL equivalent?
- asdasf 13y ago>(albeit waay slower than MySQL in the past). That isn't even remotely close to true. Single threaded "insert 10,000 rows" benchmarks had postgresql slightly slower, not "waay slower". Concurrent access benchmarks have always had postgresql faster than mysql.
- sergiosgc 13y agoI'm old. When I say in the past, I mean pgsql 4 ;-)
- asdasf 13y agoI'm old. I know that there was no postgresql 4. The first release of postgresql was 6, and what I said was accurate as of the release of version 6, in 1997 or 1998.
- sergiosgc 13y ago[slow bow, with flamboyant hat salute] :) I never expected the very geek joke to be understood. Very cool! On a more serious note, I didn't mean to support the ever old myth that postgresql is slower than mysql. It hasn't been the case for over ten years, and all the while being a better database in every aspect. However, go back enough and it was slower (than mysql) for real-world loads. You could get near enough if you disabled fsync, but then were negating many of the advantages of pgsql. Even then, in the old days, table based locking would kill a real database. Table based locking was solved early enough ('98? '99? around that time). Fsync was solved with WAL, in the performance effort of the early 7.x series. It was around late '2000 that I came back to postgresql for good. Until then, it was either mysql for a quick hack or oracle for anything serious. Since then, it's postgresql for everything (I don't deal with the scenarios where Oracle still leads).
- BlobbleBlab 13y agoI can tell you from experience that joins are also slow in Oracle Database Enterprise Edition. Joins are slow. A nicely normalized relational data model has many advantages, but speed is not one of them.
- mbroberg 13y agoSo then does this mean that PostgreSQL is to SQL like CouchDB is to NoSQL?
- wcummings 13y agoI think a lot of the NoSQL hate comes from people who don't understand the use-case, and don't build High Availability/Low Latency systems. A lot of the things I work on require pre-summarized data to provide fast response times at scale (aggregating normalized data would be too slow) and a NoSQL DB (not necessarily Mongo, I prefer Couchbase for most things) with a simple but flexible data model that gives more control to the developer Just Makes Sense.
- monstrado 13y agoComparing apples to oranges really, but I suppose it's more accurate if you take into account all the people who are (or could be using) a relational database without much issue and then switching over. The article is right though, I've heard scaling MongoDB into the hundreds of gigs to terabytes is an absolute nightmare for the operation team, the software is actually very cool when dealing with reasonably sized data that has an elastic structure. There are other "NoSQL" databases out there that pay more attention to scale, like HBase. My HBase cluster is just over a TB compressed, consistently churning 6k requests a second, without an issue.
- Roboprog 13y agoMaybe that explains why Oracle is pushing MySQL, then. See? Open Source DB bad, must get the real thing from Oracle! Or just use PostgreSQL :-)