quassnoi

Lack of access to central sewage system does not necessarily imply a lack of a "normal WC". This figure includes households which use individual sewage systems ending up in septic tanks or cesspools, and is not significantly larger than 20% of the U.S. households that don't have access to a centralized sewer either: https://nepis.epa.gov/Exe/tiff2png.cgi/P1004624.PNG?-r+75+-g... Of course latrines are a thing in rural Russia and, sadly, children do drown in them, but this article does not support your original claim.

There is no advantage: the modern GPU can build thousands of full page resolution fractals per second while SQL takes seconds to produce a hundred by hundred characters ASCII art.

It's just a New Year SQL picture post, something I do every new year on my blog.

Sure, especially if you nest the sets using a datatype indexable with an R-Tree index: http://explainextended.com/2009/09/29/adjacency-list-vs-nest...

However, nested sets is still very expensive to update which may be a deciding factor if your hierarchy is subject to frequent change (like, a folder-like structure with the folders being moved here and there) and you don't need to query for "all descendants" often.

Thank you all guys and happy New Year!

OK, OK. Most of your workloads are HDD bound.

"HDD bound" here means that not all data fits into the cache.

Given the average RAM size on even an entry-level server (some 16 or maybe even 8 GB) and the average size of a database (same 8GB on http://musicbrainz.org/, the largest online music database), I wouldn't claim that it's "most workloads".

What do you actually get from using MySQL?

Convenience of the complex queries. If you need anything more than a simple key-value access, you'll in fact need to implement the query engine yourself if you decide to use NoSQL.

Note that the author specifically mentioned that they use HandlerSocket for fast key retrievals and SQL for complex queries, and both methods work together on the very same database.

So, if you're bypassing most of the bits of an RDBMS, what do you actually gain?

Speed.

Is the InnoDB storage engine that much superior compared to other products?

InnoDB is faster than MySQL over InnoDB.

This is a single-value KVS within a field, not a replacement for SQL. You still need to write an SQL statement to access it.

InnoDB was initially designed as a KVS. MySQL only added the SQL frontend to it.

Initially, MySQL also supported BerkeleyDB, another KVS, but as for now they dropped the support.

From InnoDB's point of view, MySQL is just a client, as is HandlerSocket the author is using.

PostgreSQL's storage engine is much more closely integrated into the system and has no low-level API easily available from the outside.

All major engines have collations and collation-specific behavior (which can be confusing). However, this one I saved for the next post (with another set of completely unrelated pictures).

All "wrong" queries are wrong in PostgreSQL as well (and #3 won't even parse). The workarounds for #4, #3 and #2 are different for PostgreSQL, the others will work.

This is very often "expected to work". Just search StackOverflow for FIND_IN_SET. Here are just three random hits:

http://stackoverflow.com/questions/4037145/mysql-how-to-sele...

http://stackoverflow.com/questions/3946831/mysql-where-probl...

http://stackoverflow.com/questions/3734161/mysql-select-wher...

None actually "blames" MySQL for that. The article just describes what comes to the beginning developer's mind first, and tells the correct way to do that.

> He's cheating a bit. He created two tables each with 1 million rows, offset by 999,000. (i.e. 1 - 1,000,000 and 999,000 - 1,999,000)

Of course this is cheating, but only to demonstrate the point :)

> But this is very unlikely in the real world. In the real world you use sequential IDs, and almost all the IDs exist. So seek is not useful.

Yes, and that's why the HASH JOIN is almost always used instead by the real optimizers :)

However, there are situations when the gaps are expected. Say, the right recordset is small compared to the left one. Usually, a NESTED LOOPS is used in this case with the right recordset leading, but each loop uses a full-fledged seek from the top of the B+Tree which can be inefficient when the right recordset is not so small.

Using a statistics-assisted MERGE JOIN in this case would help to mix the seeks and the scans.

Say, if the statistics say "current right is 500, current left is 300, there are 40000 records from 0 to 1000 in the left recordset", we know that most probably we'll need to skip 8000 records to reach the right pointer and we better seek.

But if on next step it will say "current right is 501, current left is 500, there are 40000 records from 0 to 1000 in the left recordset", we know that there are only 40 records left which are most probably in one page and we can scan instead.

It can be thought of as an improved NESTED LOOPS which caches the current position.

Unfortunately, with a B+Tree index, you cannot jump in powers of 2 (well, you can but only within one page). To find a record with a given offset you'd need to traverse the whole linked list of the leaf-level pages, and that's what MERGE JOIN does by default.

However, this solution would be nice if you had a sorted recordset which allows efficient positional seek (for instance, a materialized result of the previous operation which required sorting).

I'm currently #26 on the rep list and I am employed (which means there is a workplace I have to go to every day and be there during the business hours).

I cannot tell much about my current job's details (I'm bound by an NDA), but mostly it involves not coding as such but rather answering the other people's programming-related questions, in other words exactly what I'm doing on Stack Overflow.

I also have a blog where people ask me the questions and I answer them.

SO and the blog just help me to be in form for the job: almost any problem the people contact me with at work I have been asked about (and answered) a dozen times on SO and the blog.

Thanks!

Hi, I'm the author of the blog.

Actually, most askers (both on Stack Overflow and on the site) are either the ORM developers or the people who need to deal with huge volumes of data (financial reports etc).

As with every other abstracting tool, it's perfectly OK to rely on an ORM — as long as you know which load can it handle and under which carpet the service hatch is.