7 ms·

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

by west0n 2y ago

mewpmewp2 2y ago
What? It should be fine to join that amount and more, given you have proper indexes.
- baq 2y ago
  This was valid advice in MySQL 3 days.
  - Twirrim 2y ago
    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.
- marcosdumay 2y ago
  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.
  - sumtechguy 2y ago
    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'.
    - marcosdumay 2y ago
      "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.
      - sumtechguy 2y ago
        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'.
cogman10 2y ago
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).
- Izkata 2y ago
  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.
  - cogman10 2y ago
    Didn't MySQL switch to InnoDB as the default engine a long time ago (my googling says 2010)? Does InnoDB have the same limitations?
    - Izkata 2y ago
      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.
      - erik_seaberg 2y ago
        InnoDB also supports transactions.
steve_adams_86 2y ago
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.
- west0n 2y ago
  this is so called "Materialized View"
bearjaws 2y ago
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.
cess11 2y ago
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.

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

by west0n 2y ago

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

mewpmewp2 2y ago

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

baq 2y ago
This was valid advice in MySQL 3 days.
- Twirrim 2y ago
  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.
marcosdumay 2y ago
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.
- sumtechguy 2y ago
  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'.
  - marcosdumay 2y ago
    "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.
    - sumtechguy 2y ago
      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'.

cogman10 2y ago

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).

Izkata 2y ago
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.
- cogman10 2y ago
  Didn't MySQL switch to InnoDB as the default engine a long time ago (my googling says 2010)? Does InnoDB have the same limitations?
  - Izkata 2y ago
    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.
    - erik_seaberg 2y ago
      InnoDB also supports transactions.

steve_adams_86 2y ago

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.

west0n 2y ago
this is so called "Materialized View"

bearjaws 2y ago

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.

cess11 2y ago

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.