When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it !
- Do I need to do SQL_NO_CACHE ?
- Is it gonna increase performance and insert rate?
@randy is correct in his assertion because I learned from Baron Schwartz awhile ago that caching buffers for queries (query cache) and caching for data and indexes (MyISAM Key Buffer [indexes only], InnoDB Buffer Pool [data and indexes)]) are orthogonal. In other words, one has nothing to do with, and has no bearing on, the other. @randy gets +1 for this.
If you are doing bulk INSERTS, I would like to make two suggestions:
SUGGESTION #1 : Use extended INSERTs
If you have 30,000 INSERT to make, please do not do 30,000 INSERTs. Try to bunch up 500-1000 rows at a time. This is what mysqldump does when creating a dump file so that when a table is being reloaded, it has hundreds or thousands of rows being inserted.
So, instead of
Try to group them like this
The more you group together, the better it will be for the next suggestion...
SUGGESTION #2 : Use a bigger bulk insert buffer
Most people forget to set the bulk insert buffer. The default is 8M. You can really improve performance for extended INSERTs by increasing it to something like
Give it a Try !!!
SQL_NO_CACHE is only relevant for SELECT statements. It is not an option for INSERT or LOAD DATA statements. "The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache"
Moreover, the query cache itself has to be enabled, then is invalidated when the underlying dataset has changed.
If you're performing a series of SELECT statements at the same time as the bulk load, then you will save a few microseconds by using SQL_NO_CACHE, but it is only an option as part of the SELECT.