MySQL 8.0 Performance Degradation with JSON_ARRAYAGG on High Cardinality Data
I'm wondering if anyone has experience with I'm writing unit tests and I've encountered a strange issue with I'm deploying to production and Does anyone know how to I'm stuck on something that should probably be simple....... I'm experiencing significant performance degradation when using the `JSON_ARRAYAGG` function in MySQL 8.0 on a table with high cardinality. The query is taking several seconds to execute and is causing timeouts in my application. My dataset consists of over 1 million rows, and I need to aggregate data from a `sales` table that contains a JSON column with various product attributes. The query looks something like this: ```sql SELECT order_id, JSON_ARRAYAGG(product_attributes) as products FROM sales GROUP BY order_id; ``` When I run this query, it works correctly but takes way too long. I’ve noticed that the `EXPLAIN` output shows a full table scan and the temporary table size is quite large, which seems to be the culprit. To troubleshoot this, I’ve tried adding indexes on both `order_id` and the JSON column, but it hasn’t improved the performance. Here’s the index I created: ```sql CREATE INDEX idx_order_id ON sales(order_id); CREATE INDEX idx_json_attributes ON sales((JSON_UNQUOTE(JSON_EXTRACT(product_attributes, '$.attribute')))); ``` Despite these indexes, the performance is still lacking. I’ve also looked into MySQL’s configuration settings related to `tmp_table_size` and `max_heap_table_size`, but increasing these values hasn’t made a substantial difference. Is there a better way to optimize the use of `JSON_ARRAYAGG` in MySQL, especially with high cardinality datasets? Could there be an alternative approach that I should consider for aggregating this JSON data more efficiently? My development environment is Ubuntu. How would you solve this? I'm developing on Ubuntu 20.04 with Sql. Any suggestions would be helpful. I'm working with Sql in a Docker container on CentOS. Could this be a known issue? I recently upgraded to Sql LTS. Is there a better approach? For reference, this is a production REST API. Could someone point me to the right documentation?