News for hackers. Stuff that matters.
Re:Your database skills are not 'good to have' by ergonaught on Monday April 01, 2024 @10:36AM (#39894588)
Every time I read someone's "horror story" of "queries that used a lot of joins, like even 3 or 4 tables", I...well, sigh, at this point, I guess. It's been a very long time since I needed advanced SQL to do things that probably shouldn't be happening in the database anyway, but the fundamentals seem largely foreign concepts today (speaking generally), and they are definitely still relevant and enormously helpful.
Re:Your database skills are not 'good to have' by rhdunn on Monday April 01, 2024 @10:58AM (#39894796)
Doesn't that happen when you use a highly normalized database table design, though where you create tables to avoid repeating data? Or if you have a lot of 1/many to many relationships in mapping tables. I.e. you'll end up with a lot of tables you need to join on to collate that data (authors, subjects, languages, etc.) to display to the user.

You could break the joins into three parts:

1. searching/filtering -- which tables you need to join on to match the selected queries;

2. sorting -- determining which column(s) of data to sort the results by; and

3. displaying -- which tables you need to join on to retrieve the information needed to display the information to the user.

These may be different or have overlaps.

Re:Your database skills are not 'good to have' by stouset on Monday April 01, 2024 @11:03AM (#39894870)
I think they’re saying that JOINing over three or four tables is really just not that big a deal for most situations, particularly if you have the needed indices.
Re:Your database skills are not 'good to have' by rhdunn on Monday April 01, 2024 @11:09AM (#39894940)
Yeah. That's something you would expect databases to be good at. Expecially as normalization and many-to-many relationships make multiple JOINs common, which was my point.
Re:Your database skills are not 'good to have' by ergonaught on Monday April 01, 2024 @11:05AM (#39894908)
Joins are rarely the problem. See the sigh.
Re:Your database skills are not 'good to have' by boesboes on Monday April 01, 2024 @10:46AM (#39894685)
That seems to be the exact point the author makes: you need to know some fundamentals to properly use databases. Even if you are not doing super complex stuff.
Re:Your database skills are not 'good to have' by ergonaught on Monday April 01, 2024 @11:04AM (#39894885)
I was certainly not disagreeing with the author.
Re:Your database skills are not 'good to have' by west0n on Monday April 01, 2024 @10:52AM (#39894747)
lesson: DONOT join more than 3 tables in MySQL, lol
Re:Your database skills are not 'good to have' by steve_adams_86 on Monday April 01, 2024 @11:09AM (#39894942)
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.

Re:Your database skills are not 'good to have' by west0n on Monday April 01, 2024 @11:14AM (#39894999)
this is so called "Materialized View"
Re:Your database skills are not 'good to have' by bearjaws on Monday April 01, 2024 @11:30AM (#39895199)
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.

Re:Your database skills are not 'good to have' by cogman10 on Monday April 01, 2024 @11:05AM (#39894900)
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).

Re:Your database skills are not 'good to have' by Izkata on Monday April 01, 2024 @11:30AM (#39895197)
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.

Re:Your database skills are not 'good to have' by cogman10 on Monday April 01, 2024 @11:35AM (#39895271)
Didn't MySQL switch to InnoDB as the default engine a long time ago (my googling says 2010)? Does InnoDB have the same limitations?
Re:Your database skills are not 'good to have' by Izkata on Monday April 01, 2024 @11:39AM (#39895331)
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.

Re:Your database skills are not 'good to have' by erik_seaberg on Monday April 01, 2024 @03:42PM (#39898278)
InnoDB also supports transactions.
Re:Your database skills are not 'good to have' by mewpmewp2 on Monday April 01, 2024 @11:04AM (#39894890)
What? It should be fine to join that amount and more, given you have proper indexes.
Re:Your database skills are not 'good to have' by marcosdumay on Monday April 01, 2024 @11:42AM (#39895376)
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.

Re:Your database skills are not 'good to have' by sumtechguy on Monday April 01, 2024 @01:47PM (#39896876)
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'.
Re:Your database skills are not 'good to have' by marcosdumay on Monday April 01, 2024 @10:37PM (#39901852)
"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.

Re:Your database skills are not 'good to have' by sumtechguy on Tuesday April 02, 2024 @09:09AM (#39905336)
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'.
Re:Your database skills are not 'good to have' by baq on Monday April 01, 2024 @11:22AM (#39895105)
This was valid advice in MySQL 3 days.
Re:Your database skills are not 'good to have' by Twirrim on Monday April 01, 2024 @11:45AM (#39895423)
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.

Re:Your database skills are not 'good to have' by cess11 on Monday April 01, 2024 @02:45PM (#39897624)
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.