MySQL Forums
Forum List  »  PHP

Re: Preferred Storage Engines for Large Inserts, Update, Delete and Select
Posted by: Rick James
Date: June 30, 2015 05:12PM

Engine? The answer is almost always InnoDB for the real tables.

However, there are cases where you might load en masse into MyISAM or MEMORY to cleanse, normalize, etc the data before copying into the real tables.

LOAD DATA is an excellent, fast, way of blindly loading 50K rows into a table.

If you use INSERT statements, batch 100 rows at a time; that will be 10x faster than individual row inserts.

50K may not be easy at first, but it is a pretty simple with enough tricks. I would expect 1 minute to be enough. If you needed to INSERT a million rows an hour, I would point you at
http://mysql.rjweb.org/doc.php/staging_table

For big DELETEing, _many_ DBAs write the obvious code, then come looking for help. It is such a common problem, I wrote this blog:
http://mysql.rjweb.org/doc.php/deletebig
If you are deleting based on "time", see
http://mysql.rjweb.org/doc.php/partitionmaint

Options: ReplyQuote


Subject
Written By
Posted
Re: Preferred Storage Engines for Large Inserts, Update, Delete and Select
June 30, 2015 05:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.