Hacker News Reader

Comment by west0n | Hacker News Reader

lesson: DONOT join more than 3 tables in MySQL, lol

Why not? I've worked with a database where we needed to join more than double that on some common queries, in part because financial stuff tends to touch several other business domains at once.

The explains can be hairy at first but once you've started systematically analysing a query and know some of the quirks of the MySQL query planner you'll manage to identify missing indices and squeeze out very, very good performance.

That was a database where we had warm tables bigger than fifty million rows. I personally migrated it from single instance MySQL 5 to a MySQL 8 clustered rig and optimised hundreds of queries written by people learning basics on the job, going from horrifying to very reasonable latencies as seen from the web clients.

Wait... why?

Admittedly I'm mostly using MSSQL, but joining multiple tables seems a pretty natural thing to do (assuming you are joining on well indexed tables).

Table locks.

This is specific to the default MyISAM engine, where it locks all tables used in a query for the duration of the query. So two queries that touch the same table can't run concurrently.

You may never hit this as a problem if your queries are fast enough, but it is something to be aware of.

Didn't MySQL switch to InnoDB as the default engine a long time ago (my googling says 2010)? Does InnoDB have the same limitations?

I actually didn't realize they switched (we're still on an ancient version and unable to upgrade for now).

InnoDB uses row-level locks, so it's extremely unlikely to cause such issues.

InnoDB also supports transactions.

You can join dozens of tables with almost no impact?

The only issue is joining with aggregations and sorting, that's where things get ugly with MySQL.

In my experience (I know nothing about this) if I needed to join a lot, I’d actually create new tables which routinely aggregated that data into tables I could query more efficiently instead. Especially if it was analytical data which was used for things like dashboard reporting.

It seems excessive at first but the performance is so dramatically better that it makes a lot of sense. Especially if the queries are part of your hot path. I always thought of it like having core business logic tables, then utility tables which are essentially derived from those core tables.

this is so called "Materialized View"

What? It should be fine to join that amount and more, given you have proper indexes.

This was valid advice in MySQL 3 days.

People love to keep trotting it out, which is annoying. "Can't use MySQL because <thing-that-hasn't-been-true-for-over-a-decade>", which I guess is arguably adjacent to other points in the article about the stuff databases have added over the years:

Check in on your databases periodically, and refresh your memory. There's a very real chance they've picked up features that'll make your life a lot easier.

More than this.

Joining the tables in the database should perform better than joining them on the application layer. If it doesn't, there's something very odd.

This holds for any production ready DBMS. Even the ones that don't care about performance or preserving your data.

As a wizened old grey beard once told me 'almost always with SQL, it depends'. Basically you have to revisit your ground truth all the time. Sometimes keeping stuff client (like a lookup table) is the right way. Most of the time server is better. But not always. 'it depends'.

"Odd" is not "wrong".

But wrong is almost always odd, so the normal expectation is that you understand the oddity on your system. Otherwise you are blind to problems.

Anyway, it's also not common for an application to have "reduce total CPU usage" as a goal. Available CPU on the database is way more valuable than on the application server, and so it makes sense to trade them up.

In most SQL cases your limiting factor will be your network. So you want to minimize what is coming over (usually). However, that tradeoff is time. If it takes more time for your client to render than it would just to send it you usually let the server do it. For example if I have a 50 row lookup with 10 columns table that is joined to a 300k row table, that for some reason must be returned to the client. Now given those conditions. It might be faster to glue it back together on the client. It just depends on how much is in those 50 rows. Just bunch of ints (probably not). A few 400 byte strings? That could be interesting and faster to do on the client. Like he said 'it depends'.

Reply on news.ycombinator.com