MySQL 8.0: Unexpected Performance Drop with Multi-Table INSERT Statements
I'm reviewing some code and I'm trying to debug I recently upgraded to MySQL 8.0 and noticed a significant performance drop when executing multi-table `INSERT` statements... Specifically, I am trying to insert data into two related tables: `orders` and `order_items`. The following query works in previous versions, but now it's taking much longer than expected. ```sql INSERT INTO orders (customer_id, order_date) VALUES (1, NOW()), (2, NOW()); INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 101, 2), (LAST_INSERT_ID(), 102, 1); ``` In this case, I am inserting new orders and then attempting to insert items for those orders using `LAST_INSERT_ID()`. In my testing, this approach took around 10 seconds to execute, while previously it was almost instantaneous. I have verified that there are no locks or other transactions affecting the performance by running `SHOW PROCESSLIST;` before and during execution, and everything seems fine. I tried breaking down the operations into separate transactions and even pre-fetching the `LAST_INSERT_ID()` to avoid potential issues, but that did not result in any performance improvement: ```sql START TRANSACTION; INSERT INTO orders (customer_id, order_date) VALUES (1, NOW()); SET @last_id = LAST_INSERT_ID(); INSERT INTO order_items (order_id, product_id, quantity) VALUES (@last_id, 101, 2), (@last_id, 102, 1); COMMIT; ``` I also checked the `innodb_buffer_pool_size` and `innodb_log_file_size`, and they seem to be set to reasonable values for my dataset. I am using a moderate amount of data, approximately 100k records in the `orders` table and 500k in `order_items`. I am wondering if there are any changes in MySQL 8.0 regarding `INSERT` performance, or if there is something specific I might need to adjust in my setup. Any advice on optimizing this process would be greatly appreciated! Additionally, I received the following warning: "Warning: Last insert ID returned 0; check for errors or the inserted data." I have no errors logged in my MySQL error log, which adds to my confusion. I'm coming from a different tech stack and learning Sql. I'd really appreciate any guidance on this. I'm working on a service that needs to handle this.