Everybody thinks SQL joins are slow-is it because MySQL doesn't have hash joins? | Hacker News Reader

Everybody thinks SQL joins are slow-is it because MySQL doesn't have hash joins?

use-the-index-luke.com

41 pointsfatalmind24 comments

No, the author has misunderstood "joins are slow." (Edit: not the post author, but the HN submitter. This reaction made more sense before the submission title was changed.)

The point is that joins are slow _when you scale past one machine_ because the data you are joining will be on different nodes.

(Yes, I'm familiar with pk-based sharding or entity groups, or even more sophisticated partitioning like volt's. None of these can always always be made to fit your data; I'm talking about the general case here.)

Yeah, scaling past one machine will make your joins slow. But is that really the _only_ thing that makes joins slow? I thought the broader performance problem with joins had more to do with the costs of carrying out the relational algebra to satisfy the query. The nested loops algorithm for joining rows is a case in point.

I think the author's point is that MySQL JOINs are slow if you are joining more than 2 tables.

More mature databases like Oracle or PostgreSQL can eat 4 or 5 way JOINs for breakfast.

I definitely don't have trouble finding queries that are 50x slower on PgSQL than MySQL so I don't think it's as simple as saying one is more mature than the other in all ways.

That said, if you're doing N-way joins with N big you probably want bitmap index support too, which is another thing missing in MySQL, although I've written libraries that, for certain specialized cases, can fake it.

Can you provide me with one or two examples that are 50x slower on pg than my? We're a postgres-heavy shop, and would be interested in finding solutions to any such problems that you can identify.

> I definitely don't have trouble finding queries that are 50x slower on PgSQL than MySQL

I would love to see an example of this, given the same data, schema, similar tuning params, etc.

Agreed. It wouldn't be very hard to find a query on a tuned MySQL box that outperforms Postgres on an untuned system. Postgres's base configuration has always been on the conservative side of tuning.

Postgres has bitmap index support baked in as well and it works really well. I'd be awfully interested in knowing where MySQL beats it by 50x at anything.

Some MySQL vs PG tests done by Domas (of Facebook & Wikipedia fame) show some interesting differences between MySQL and PG for an in-memory read only test. I don't think there are any 50x differences though.

http://dom.as/2010/11/08/random-poking/

The tough thing about comparing these two is that it is hard to find a benchmark (I don't know of one) that works similarly with them both. Sysbench is somewhat of a standard for testing MySQL configs but Postgres does not handle it well.

Though I tend to prefer Postgres don't get me wrong - MySQL has some advantages.

For raw pkey lookups, especially range selects against pkeys with lower amounts of concurrency InnoDB's speed is unmatched. It should be given that it is completely laid out on disk specifically for that to the detriment of other features.

MySQL's replication is extremely sturdy. On paper the older method (statement-based) sounds incredibly fragile but in practice it works extremely well and has been proven on countless projects. mmm makes it almost too easy to setup replication and failover.

I feel that MySQL stalled out for several years in the 5.0-5.1 period where poorly engineered features were bolted on to create a product with too many pathological cases that destroyed performance to keep track of. All these features were available but experience taught you to avoid most joins, avoid most subselects, avoid most usages of views, etc.

That said, v5.5 has a lot of nice improvements and can actually scale up to more than 8 cores (I've gotten linear improvement up to about 32 cores and that is what Oracle puts in their white papers as well). Percona and Facebook are releasing nice patches and branches, and forks like Drizzle are reaching GA and doing good things as well. So I think it is headed in the right direction again.

Aggregates that have to do full table scans are notoriously slow in Postgres due to how multi-version concurrency control is implemented.

Any time you comment out a WHERE clause on a big table while performing an aggregate report, Postgres chokes.

Compared to MyISAM? Sure for certain aggregate functions, but it's too easy to point out reasons for avoiding MyISAM: fragmentation, no transactions, table locks on writes, complete table rewrites to change columns or indexes, only scales up to about 8 cores.

Compared to InnoDB? You are incorrect, Postgres is faster, even for a raw count(*), even when comparing against the InnoDB plugin and not the ancient InnoDB builtin.

I have access to tuned TB+ DBs of both types and am happy to disprove any specific examples you can provide.

I just read up on this and it appears you are right, my issues are fixed in more recent versions of PostgreSQL. Apologies.

What do your libraries do to fake a bitmap index? When you say libraries, what layer of the stack are you referring to? Data access layer (e.g., via an ORM)?

Got any data to back that claim up?

Well sure, if you don't change its default configuration and leave Postgres tuned for a 486.

And why is Postgres tuned by default for a 486? I face this with databases as small as 2 to 5GB; Postgres needs its parameters tuned to do the correct join procedures using indexes.

How is PG going to compete with mySQL in the basic webapp market when mySQL is download-and-run ready? Or does PG always want to be a niche player? I'd rather see it grow because if your app ever grows, you'll be happy to have a real full-featured relational database someday.

I recently had a similar problem. Users wanted to export a highly normalized structure to a csv file for munging in a spreadsheet. They were limited to 200 rows, because a thousand rows would bring everything to its knees. My first cut was to move from Hibernate to pure SQL, but if I tried 1000 rows it went away for a long time. I dont know how long because I killed it after 15 minutes. The real killers seemed to be lef outer joins.

I then split the query into several temporary tables. The first table contained all the rows that I was interested in. The other tables replaced the left outer joins and nasty beasts like group concatenates and queries that turned aggregate results into separate fields. The temp tables were used to update the first table. Runtime went from a significant part of an hour to under 5 seconds.

Based on the majority (since the title is going for "everybody") of people who drop into #mysql on any given day to ask about slow queries, I'd argue that the majority of people who thinks MySQL joins are slow still don't have proper indexes and are still using their un-tuned, distro-provided my.cnf

It's worth mentioning that MySQL (iirc) does support sort-merge joins, which aren't mentioned by this article. So it's not a case of (index) nested loops joins or nothing. While hash joins are usually significantly faster than sort-merge, they have a variety of similar characteristics, and don't suffer from the mash-the-b-tree issue that index nested loops do. Overall this makes me doubt that the lack of hash joins is the fundamental reason some people think joins are slow.

MySQL only supports nested inner loop joins.

...ah, I just bothered to check it out in more detail, and you're kind-of right. MyISAM only supports nested loops (which I have to say surprised me), but InnoDB (which, let's face it, is the backing store that allows mysql to qualify as a 'real' RDBMS) supports hash joins.

Not directly related, but this is a great site to peruse. Great code examples and a great perspective on database performance.

Is the answer in the domain name?

Reply on news.ycombinator.com