Does table size affect INSERT performance?

Question

This is a question only to ask:

Prohibition of all intermediate to advanced topics or methods (clustered indexes, BULK INSERTS, export / import tricks, etc.), does INSERT do more the more the table grows?

This assumes that there is only one auto-int column, ID [that is, all new INSERTED rows at the bottom, where the memory should not be shuffled to accommodate the specific row positioning].

---

It would be convenient to use the link to a good "MySQL benchmarking". I took the Oracle at school, and so far knowing little has helped me in SO.

Thanks to everyone.

Answer 1

My experience is that performance degrades if the dataset index no longer fits in memory. As soon as this happens, checks for duplicate indexes will be forced to hit the disk, and this will decrease significantly. Make a table with the same amount of data that you think you have to deal with, and do some testing and tuning. This is really the best way to find out what you are facing.

Answer 2

Yes, but this is not the size of the table as such, but the size of the indexes that matter. As the rewriting of the index begins to crash, you will notice a slowdown. A table without indexes (of course, there will never be such a thing in your database) should not get worse. A table with minimal compact indexes can grow to very large sizes without seeing deterioration. A table with many large indexes will begin to decline earlier.

Answer 3

I can share my experience. hope this helps.

I insert many rows at a time into a huge database (several million records). I have a script that prints the time before and after the implementation of the insert. well, I have not seen a drop in the speeches.

Hope this gave you an idea, but I'm on sqlite not mysql.

Answer 4

The speed does not change, if MySQL can update the full index in memory, when it starts to change the index, it becomes slower. This is what happens if you instantly restore a huge index using ALTER TABLE .