14 ms·

I’ll bite at the bait: I prefer SQLite in most cases too, but why have you forbidden MySQL?

zimpenfish 4y ago
Mainly residual trauma from the early days - I was a big proponent in the early days (back when you could get personal support from Monty) but too many security holes, corruption bugs, etc., lead me to the arms of SQLite + Postgres and I've stayed there ever since. It's entirely possible MySQL is a superb DB engine now and won't cause me any more trauma but ...
- chrsig 4y ago
  There's a world of difference between the myisam & innodb storage engines. Not encouraging you to return or anything, just throwing it out there that many/most storage related problems were because of myisam.
  - zimpenfish 4y ago
    Oh, definitely, but it was some time after innodb arrived and it brought a different set of (undoubtedly teething) problems. It was kinda damned if you do, damned if you don't at that time. Life was just easier without MySQL.
    - Cyberdog 4y ago
      Fair enough I suppose. I guess I've been lucky enough to not encounter any show-stopping problems with MySQL, at least that I can recall. Maybe you started using it well before I did. For what it's worth, in the cases where you need a client-server database, I know that Postgres is supposed to be "better," but MySQL is everywhere and it's always been up for almost any task I've thrown at it. I've found it to always be sufficiently good enough.
      - zimpenfish 4y ago
        That's reminded me of one problem I had with it back in 2010 - it didn't have indexes on expressions[1]. We had a few hundred million rows with unindexed timestamps (I forget if they were MySQL-native or just ISO8601 strings) and the higher ups wanted a daily aggregate report of some stuff which was, obviously, slow to generate because of having to truncate all the timestamps to just YMD[2]. For Postgres, I would have just created an index on (the equivalent of) `truncate(YMD, timestamp)` (and I demonstrated this as a POC to the higher ups as a good reason to switch to Postgres. But alas, no traction.) [1] Seems to have arrived some time around 2013-14? [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived.
        chrsig 4y ago
        mysql has a datetime type that can be an index, and should be able use for a range scan. that is, with the appropriate data type, it sounds like you would've had a better time... > [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived. The real benefit of postgres*: offline transactional schema changes. mysql would've done a table copy in order to change it. if mysql supported offline schema changes, perhaps you'd have been able to change the data type, solving the root cause of the problem. * I haven't had the pleasure of using postgres in production, so I can't speak to how effective any given feature is -- only what the marketing appeal is.

Mainly residual trauma from the early days - I was a big proponent in the early days (back when you could get personal support from Monty) but too many security holes, corruption bugs, etc., lead me to the arms of SQLite + Postgres and I've stayed there ever since. It's entirely possible MySQL is a superb DB engine now and won't cause me any more trauma but ...

chrsig 4y ago
There's a world of difference between the myisam & innodb storage engines. Not encouraging you to return or anything, just throwing it out there that many/most storage related problems were because of myisam.
- zimpenfish 4y ago
  Oh, definitely, but it was some time after innodb arrived and it brought a different set of (undoubtedly teething) problems. It was kinda damned if you do, damned if you don't at that time. Life was just easier without MySQL.
  - Cyberdog 4y ago
    Fair enough I suppose. I guess I've been lucky enough to not encounter any show-stopping problems with MySQL, at least that I can recall. Maybe you started using it well before I did. For what it's worth, in the cases where you need a client-server database, I know that Postgres is supposed to be "better," but MySQL is everywhere and it's always been up for almost any task I've thrown at it. I've found it to always be sufficiently good enough.
    - zimpenfish 4y ago
      That's reminded me of one problem I had with it back in 2010 - it didn't have indexes on expressions[1]. We had a few hundred million rows with unindexed timestamps (I forget if they were MySQL-native or just ISO8601 strings) and the higher ups wanted a daily aggregate report of some stuff which was, obviously, slow to generate because of having to truncate all the timestamps to just YMD[2]. For Postgres, I would have just created an index on (the equivalent of) `truncate(YMD, timestamp)` (and I demonstrated this as a POC to the higher ups as a good reason to switch to Postgres. But alas, no traction.) [1] Seems to have arrived some time around 2013-14? [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived.
      - chrsig 4y ago
        mysql has a datetime type that can be an index, and should be able use for a range scan. that is, with the appropriate data type, it sounds like you would've had a better time... > [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived. The real benefit of postgres*: offline transactional schema changes. mysql would've done a table copy in order to change it. if mysql supported offline schema changes, perhaps you'd have been able to change the data type, solving the root cause of the problem. * I haven't had the pleasure of using postgres in production, so I can't speak to how effective any given feature is -- only what the marketing appeal is.