7 ms·

At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason, dependency reason, boss-likes-MySQL reason...). They are all handicapped by MySQL

At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason, dependency reason, boss-likes-MySQL reason...). They are all handicapped by MySQL featureset, and can't update to 8 yet. If they had used Postgres some years ago, they would get: - JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) - Window functions (available in MySQL 8x only, while this has been available since Postgres 9x) - Materialized views, views that is physical like a table, can be used to store aggregated, pre-calculated data like sum, count... - Indexing on function expression - Better query plan explanation

Macha 7y ago
Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing (you see GROUP_CONCAT shenanigans being used instead). For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.
- colanderman 7y ago
  Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned to remain that way to serve that purpose. If you can, prefer using views to enhance readability (and testability as a bonus). PostgreSQL views (unlike those in MySQL) do not prevent optimization across them.
  - zkomp 7y ago
    No, CTEs are not planned to remain a barrier, this is already fixed in the next version which is in feature freeze right now. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
    - paulddraper 7y ago
      This is the best news I've heard all week.
      - munk-a 7y ago
        I have tried to express my joy at this news to my less SQL literate co-workers... that failed so I wanted to let it out here. This is the best news, I am overjoyed!
    - jeltz 7y ago
      My favorite feature of PostgreSQL 12 except perhaps REINDEX CONCURRENTLY, but I am very biased since I was involved in both patches (both were large projects involving many devs and reviewers). It is awesome to finally see both land.
    - colanderman 7y ago
      Oh wow, that is news to me! A welcome change.
  - hans_castorp 7y ago
    Which is very often a good thing. I have tuned more than one query by moving a sub-query/derived table into a CTE. What bothers me more, that a CTE prevents parallel execution, but I think that too is fixed with Postgres 12
evanelias 7y ago
> Indexing on function expression MySQL 5.7 fully supports this. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... and https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda... > JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) Actually MySQL 5.6 doesn't support this, but 5.7 does, quite well: https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
- hans_castorp 7y ago
  Indexing a generated/computed column is not the same as creating an index on an expression. If you want to support several different expressions you need to create a new column each time. Additionally, an ALTER TABLE blocks access to the table. Indexes can be created concurrently while other transactions can still read and write the table. But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.
  - evanelias 7y ago
    > If you want to support several different expressions you need to create a new column each time Yes and no. Generated columns in MySQL can optionally be "virtual". An indexed virtual column is functionally identical to an index on an expression. > Additionally, an ALTER TABLE blocks access to the table. It depends substantially on the specific ALTER and version of MySQL. Many ALTERs do not block access to the table in modern MySQL; some are even instantaneous. > But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that. What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)
    - hans_castorp 7y ago
hans_castorp 7y ago
Actually window functions were introduced in Postgres 8.4
noir_lord 7y ago
I'm stuck on 5.7 because previous dev used the worst sprocs I've seen (no exaggeration) and until I've ripped them all out I daren't move to 8, it was on 5.5 when I started but with much effort I got it tested enough to reasonably confident that 5.7 would work. It's an excruciating process though.

Macha 7y ago

Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing (you see GROUP_CONCAT shenanigans being used instead). For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.

colanderman 7y ago
Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned to remain that way to serve that purpose. If you can, prefer using views to enhance readability (and testability as a bonus). PostgreSQL views (unlike those in MySQL) do not prevent optimization across them.
- zkomp 7y ago
  No, CTEs are not planned to remain a barrier, this is already fixed in the next version which is in feature freeze right now. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
  - paulddraper 7y ago
    This is the best news I've heard all week.
    - munk-a 7y ago
      I have tried to express my joy at this news to my less SQL literate co-workers... that failed so I wanted to let it out here. This is the best news, I am overjoyed!
  - jeltz 7y ago
    My favorite feature of PostgreSQL 12 except perhaps REINDEX CONCURRENTLY, but I am very biased since I was involved in both patches (both were large projects involving many devs and reviewers). It is awesome to finally see both land.
  - colanderman 7y ago
    Oh wow, that is news to me! A welcome change.
- hans_castorp 7y ago
  Which is very often a good thing. I have tuned more than one query by moving a sub-query/derived table into a CTE. What bothers me more, that a CTE prevents parallel execution, but I think that too is fixed with Postgres 12

evanelias 7y ago

> Indexing on function expression MySQL 5.7 fully supports this. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... and https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda... > JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) Actually MySQL 5.6 doesn't support this, but 5.7 does, quite well: https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html

hans_castorp 7y ago
Indexing a generated/computed column is not the same as creating an index on an expression. If you want to support several different expressions you need to create a new column each time. Additionally, an ALTER TABLE blocks access to the table. Indexes can be created concurrently while other transactions can still read and write the table. But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.
- evanelias 7y ago
  > If you want to support several different expressions you need to create a new column each time Yes and no. Generated columns in MySQL can optionally be "virtual". An indexed virtual column is functionally identical to an index on an expression. > Additionally, an ALTER TABLE blocks access to the table. It depends substantially on the specific ALTER and version of MySQL. Many ALTERs do not block access to the table in modern MySQL; some are even instantaneous. > But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that. What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)
  - hans_castorp 7y ago

hans_castorp 7y ago

Actually window functions were introduced in Postgres 8.4

noir_lord 7y ago

I'm stuck on 5.7 because previous dev used the worst sprocs I've seen (no exaggeration) and until I've ripped them all out I daren't move to 8, it was on 5.5 when I started but with much effort I got it tested enough to reasonably confident that 5.7 would work. It's an excruciating process though.