Menu
This is a question just for the sake of asking:
Barring all intermediate to advanced topics or techniques (clustered indices, BULK INSERTS, export/import tricks, etc.), does an INSERT take longer the larger a table grows?
This assumes that there is only one auto-int column, ID [i.e., all new rows are INSERTED at the bottom, where no memory has to shuffled to accommodate a specific row positioning].
A link to a good "benchmarking MySQL" would be handy. I took Oracle in school, and so far the knowledge has done me little good on SO.
Thanks everyone.
678
Simplistically, reducing the size of the table will not have much impact on performance. There are some cases where it could make a difference. If the clustered index primary key is not ordered, then you have a fragmentation problem.
To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.
You can use the following methods to speed up inserts: If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.
3. Inserts are slower against a Heap (no Clustered index) Inserts against a table with no clustered index (heap) are optimized for saving space, rather than performance. This means that SQL Server will spend more time searching for available space than when a clustered index is used.
Yes, but it's not the size of the table per se but the size of the indices that matter. Once index rewriting begins to thrash the disk, you'll notice a slowdown. A table with no indexes (of course, you'd never have such a thing in your database) should see no degradation. A table with minimal compact indexes can grow to a very relatively large size without seeing degradation. A table with many large indices will start to degrade sooner.
58
My experience has been that performance degrades if the dataset index no longer fits in memory. Once that happens, checks for duplicate indexes will have to hit disk and it will slow down considerably. Make a table with as much data as you think you'll have to deal with, and do some testing and tuning. It's really the best way to know what you'll run into.
27
I can only share my experience. hope it helps.
I am inserting lots of rows at the time, on huge database (several millions of entries). I have a script which prints the time before and after I execute the inserts. well I haven't seen any drop in performances.
Hope it gave you an idea, but I am on sqlite not on mysql.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
