Just want to get some feedback on best practises on database row deletions and performance. I have a hotel dump of 2.5 million hotels that I get every 7 days, there is also a daily incremental dump, that can contain more than 100k hotel updates by days 5 and 6. When i get this incremental dump i n…

For answers to questions like this I believe this is where an LLM is very useful indeed. So I can't take credit for it. Although Prompt Engineer is now apparently a job... :astonished: ChatGPT pumped this out: Deleting hundreds of thousands of rows in MySQL efficiently can be challenging, but h…

Thank you so much, that gives me some ideas to try out, appreciate it. [image] Cheese: Disable or Adjust Indexes: Consider dropping indexes before the delete operation and recreating them afterward if the table is heavily indexed. This can significantly speed up the delete process. Lol, I ad…

Let us know how things turned out and which steps gave you the most acceptable performance. Been a while since I managed a DB but here would be my high-level approach. The safest approach would be to disconnect the DB from the app first, eliminating any contention for data, locked rows, dirty reads…

Great information, thank you for that. I am actually doing all this on a completely seperate database server, as the 2.5 million records are 28GB in their original JSONL file, and when inserted in the MariaDB database in a single InnoDB table it takes 35GB in the DB. So everything happens in this …

wow!! this is insane.. and here im worried about 5K records.,... hahaha this is jus another level.... first of all .. WELL DONE.... in managing such a large amount of data.. and getting it to work..... and secondly.... well done in always pushing the limits.... thanks for the inspiration Paul..…

[image] psweb: i need to go delete the 100k rows in the hotels database table by ID You need to delete by ID? Couldn't you truncate the entire table and insert again from scratch?

Have you tried a delete query based on a join. Basically syntax of: DELETE * FROM table WHERE id IN SELECT * FROM table2 Should be much faster than a repeat

I would love to, because i can bulk insert 2.5 rows in half the time as deleting 100k rows by ID currently, but because I am supplied a full hotel dump every Monday, and then Tuesday they update an incremental dump which may only have 15k records, then on Wednesday I get another incremental dump, wi…

Have you tried using my custom extension to perform updates?

[image] Hyperbytes: DELETE * FROM table WHERE id IN SELECT * FROM table2 Thanks Brian, I will give that a try for sure, did not even think that was possible, but seeing the syntax it does make some sense. Will report back findings. [image] Apple: Have you tried using my custom extension …

Database row delete performance advice

Wappler General Need Help

Apple October 23, 2024, 4:55pm 12