CODEWITHSUNDEEP
mysqloptimization
Haroldo
Haroldo

Reputation: 37377

are MySQL INSERT statements slower in huge tables?

I can see how SELECT and UPDATE statements get slower as a table grows and grows, but what about INSERT ?

Upvotes: 3

Views: 391

Answers (5)

Luis H Cabrejo
Luis H Cabrejo

Reputation: 316

If your tables are small you will be OK. But if your tables are getting big, inserts, updates will get slower, this is the procedure I used and work for me.

This issue happens even using InnoDB tables or MyISAM, not optimized for writes, and solved it by using a second table to write temp data (that can periodically update master huge table). Master table over 18 million records, used to read only records and write result on to second small table.

The problem is the insert/update onto the big master table, takes a while, and worse if there are several updates or inserts on the queue awaiting, even with the INSERT DELAYED or UPDATE [LOW_PRIORITY] options enabled

To make it even faster, do read the small secondary table first, when searching a record, if te record is there, then work on the second table only. Use the master big table for reference and picking up new data record only *if data is not on the secondary small table, you just go and read the record from the master (Read is fast on InnoDB tables or MyISAM schemes)and then insert that record on the small second table.

Works like a charm, takes much less than 5 seconds to read from huge master 20 Million record and write on to second small table 100K to 300K records in less than a second.

This works just fine.

Upvotes: -1

Saher Ahwal
Saher Ahwal

Reputation: 9237

INSERT gets slower too

Upvotes: 1

Piskvor left the building
Piskvor left the building

Reputation: 92772

In general, yes - O(1) performance is rare anywhere and keeping the indexes current has a cost.

The question is, why does it matter, and what can you do about it in your specific case?

  • don't create indexes that are never used (e.g. I keep finding tables with an additional single-column index on the primary key)
  • don't keep useless data in the table (or, if you rarely use old data, consider moving them to an archive table/database)
  • iff you have a problem with insert speed and you don't care about autoincrement-IDs, INSERT DELAYED may help you

Upvotes: 3

xil3
xil3

Reputation: 16439

INSERT gets slower too, because it has to sort out the indexes.

Upvotes: 3

Konerak
Konerak

Reputation: 39763

INSERT gets slower too, especially if you have a lot of indexes which have to be updated as well.

There is a difference between the different storage engines though: MyISAM is faster for a lot of SELECT, InnoDB is faster for a lot of INSERT/UPDATE because it uses row locking instead of table locking and the way it handles indexes.

Upvotes: 5

Related Questions