6 ms·

Because enums only work for the simplest use cases. If you replace status_id with country_id, where each country_id has more than one property (country name, I

Because enums only work for the simplest use cases. If you replace status_id with country_id, where each country_id has more than one property (country name, ISO alpha 2 code, ISO aplha 3, currency_id etc), you can see why enum isn't good enough.

toast0 8y ago
You might not even need most of that country data in your database though, it could be in your application in many cases (the fastest join your database can do is the one that you do in frontend code instead)
- dotancohen 8y ago
  I don't know why people think this. In the specific example of country, in fact I _have_ tested using a country class with hard coded maps of country codes to arrays of data. Even so, benchmarking showed the single database call with a join (MySQL 5.1 or 5.5 I think) to be faster as I was already hitting the database anyway. Don't prematurely optimize away your database's flexibility (managed content) before testing a properly-coded query.
  - toast0 8y ago
    I've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I've seen a lot of cases where just moving sorting to the frontend took a 3 second query down to 10ms and sorting on the frontend wasn't just a few ms too either. There were too many sorts available in the UI to add matching indexes in the db. If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends.
    - kthejoker2 8y ago
      What about analytics, hosting dimensional data outside your DB sounds terrible.
    - dirkgently 8y ago
      > I've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I too have seen front end of an application do a lot of dumb stuff with state of my requests. The point is, don't confuse PEBKAC with an issue with a tool.
    - mmt 8y ago
      > If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends. This may only true for data manipulation that's necessarily [1] more CPU-intensive/bound than I/O-intensive. It's difficult to imagine a situation where that would actually be true, however, in the context of a manipulation like a join. Given how much faster CPU power has increase relative to I/O, over the entire history of computing, CPU on database servers has been progressively less of an issue. This isn't to say that CPU power is infinite, and I've seen naive analyses attribute attribute performance problems to inadequate CPU power, even when it's an I/O issue masquerading as (system, not user) CPU time. In that respect, it's hard to scale a database server, in that now-rare and undervalued-by-programmers Ops knowledge of things like hardware and I/O and offloading [2] but that difficulty doesn't actually go away by moving the data around in a partly-distributed or even fully distributed system. It just drives up the cost. See also Amdahl's Law and the Fallacies of Distributed Computing (in particular the ones about bandwidth and latency). [1] as opposed to merely as-implemented, such as due to PEBKAC, as a sibling comment points out [2] e.g. why ZFS might have wonderful features, but harware RAID cards can offload a remarkably significant pressure from the CPU and main memory, when it matters most
      - scarface74 8y ago
        In that respect, it's hard to scale a database server, in that now-rare and undervalued-by-programmers Ops knowledge of things like hardware and I/O and offloading [2] but that difficulty doesn't actually go away by moving the data around in a partly-distributed or even fully distributed system. It just drives up the cost. In a cloud environment - which is relevant since he was using AWS - it’s much cheaper and easier to autoscale app servers than database servers based on load. It could also be conceivable cheaper if you’re doing non time sensitive online analytics processing instead of something that requires a real time response. On AWS, you could set autoscaling and use spot instances to scale more when the rates are cheaper for compute. Also, when scaling up a database, there is more of a potential for wasting money because of idle capacity when the resources aren’t needed. It’s painless to remove an app VM out of service. Yes I know you can also use DynamoDB, but it’s non relational, the cost of adding additional global indexes and paying for extra read and write capacity gets expensive. There’s also Serverless Aurora that auto scales but it would also get really expensive fast.
    - dotancohen 8y ago
      > just moving sorting to the frontend took a 3 second query down to 10ms I've seen this too, but the problem can often be optimized in SQL as well. For instance, if you expect that only one User will match a specific username, then adding LIMIT 1 to the query will instruct the DB to return after finding a single hit, which can significantly shorten query time. But then adding an ORDER BY clause will cause (at least MySQL) to parse the entire table anyway, even though you've got the LIMIT clause. This is just an example, one really needs to know their database and their application to make these types of decisions. But the premature optimization applies to SQL as well, if the results can be sorted/filtered/transformed in application memory better than in the database so be it.
- dirkgently 8y ago
  You are only thinking of just an application. No offense, but this is a classic case of app devs not realizing that databases exist for more than just storing data. Ever thought that databases are so used to generate reports, dashboards? What about a data warehouse? OLAP? Cubes? Aggregates? What about feeding other systems (e.g. A third party downstream system)?

toast0 8y ago

You might not even need most of that country data in your database though, it could be in your application in many cases (the fastest join your database can do is the one that you do in frontend code instead)

dotancohen 8y ago
I don't know why people think this. In the specific example of country, in fact I _have_ tested using a country class with hard coded maps of country codes to arrays of data. Even so, benchmarking showed the single database call with a join (MySQL 5.1 or 5.5 I think) to be faster as I was already hitting the database anyway. Don't prematurely optimize away your database's flexibility (managed content) before testing a properly-coded query.
- toast0 8y ago
  I've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I've seen a lot of cases where just moving sorting to the frontend took a 3 second query down to 10ms and sorting on the frontend wasn't just a few ms too either. There were too many sorts available in the UI to add matching indexes in the db. If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends.
  - kthejoker2 8y ago
    What about analytics, hosting dimensional data outside your DB sounds terrible.
  - dirkgently 8y ago
    > I've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I too have seen front end of an application do a lot of dumb stuff with state of my requests. The point is, don't confuse PEBKAC with an issue with a tool.
  - mmt 8y ago
    > If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends. This may only true for data manipulation that's necessarily [1] more CPU-intensive/bound than I/O-intensive. It's difficult to imagine a situation where that would actually be true, however, in the context of a manipulation like a join. Given how much faster CPU power has increase relative to I/O, over the entire history of computing, CPU on database servers has been progressively less of an issue. This isn't to say that CPU power is infinite, and I've seen naive analyses attribute attribute performance problems to inadequate CPU power, even when it's an I/O issue masquerading as (system, not user) CPU time. In that respect, it's hard to scale a database server, in that now-rare and undervalued-by-programmers Ops knowledge of things like hardware and I/O and offloading [2] but that difficulty doesn't actually go away by moving the data around in a partly-distributed or even fully distributed system. It just drives up the cost. See also Amdahl's Law and the Fallacies of Distributed Computing (in particular the ones about bandwidth and latency). [1] as opposed to merely as-implemented, such as due to PEBKAC, as a sibling comment points out [2] e.g. why ZFS might have wonderful features, but harware RAID cards can offload a remarkably significant pressure from the CPU and main memory, when it matters most
    - scarface74 8y ago
      In that respect, it's hard to scale a database server, in that now-rare and undervalued-by-programmers Ops knowledge of things like hardware and I/O and offloading [2] but that difficulty doesn't actually go away by moving the data around in a partly-distributed or even fully distributed system. It just drives up the cost. In a cloud environment - which is relevant since he was using AWS - it’s much cheaper and easier to autoscale app servers than database servers based on load. It could also be conceivable cheaper if you’re doing non time sensitive online analytics processing instead of something that requires a real time response. On AWS, you could set autoscaling and use spot instances to scale more when the rates are cheaper for compute. Also, when scaling up a database, there is more of a potential for wasting money because of idle capacity when the resources aren’t needed. It’s painless to remove an app VM out of service. Yes I know you can also use DynamoDB, but it’s non relational, the cost of adding additional global indexes and paying for extra read and write capacity gets expensive. There’s also Serverless Aurora that auto scales but it would also get really expensive fast.
  - dotancohen 8y ago
    > just moving sorting to the frontend took a 3 second query down to 10ms I've seen this too, but the problem can often be optimized in SQL as well. For instance, if you expect that only one User will match a specific username, then adding LIMIT 1 to the query will instruct the DB to return after finding a single hit, which can significantly shorten query time. But then adding an ORDER BY clause will cause (at least MySQL) to parse the entire table anyway, even though you've got the LIMIT clause. This is just an example, one really needs to know their database and their application to make these types of decisions. But the premature optimization applies to SQL as well, if the results can be sorted/filtered/transformed in application memory better than in the database so be it.
dirkgently 8y ago
You are only thinking of just an application. No offense, but this is a classic case of app devs not realizing that databases exist for more than just storing data. Ever thought that databases are so used to generate reports, dashboards? What about a data warehouse? OLAP? Cubes? Aggregates? What about feeding other systems (e.g. A third party downstream system)?