Poor count() performance with MySQL compared to PostgreSQL with JSON datasets

[velmu]

https://malloc.fi/mysql-poor-count-performance-postgresql-json

[gregjor]

Did you try SELECT COUNT(*) FROM trip ?

MySQL has optimizations for that, and since you’re counting the primary key column, which can’t contain NULL values, the result will be the same.

You might want to move the JSON column to a separate table with a 1:1 relationship (same primary key) to keep the row size down in the main table.

[mister_hn]

What are the advantages of using JSON instead of Blob in the DB and convert the blob in JSON in code?