what are these lines telling?
If you must use INSERT, TRy to use the form that allows multiple rows to be specified in a single statement:
INSERT INTO tbl_name VALUES(...),(...),... ;
The more rows you can specify in the statement, the better. This reduces the total number of statements you need and minimizes the amount of index flushing. This might seem to contradict the earlier remark that shorter statements can be processed faster than longer statements. But there is no contradiction. The principles here are that a single INSERT statement that inserts multiple rows is shorter overall than an equivalent set of individual single-row INSERT statements, and the multiple-row statement can be processed on the server with much less index flushing.
can you give me an example?
If you must use multiple INSERT statements, group them if possible to reduce index flushing. For transactional storage engines, do this by issuing the INSERT statements within a single transaction rather than in autocommit mode:
START TRANSACTION;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
COMMIT;
can i group them if they are through a loop?
for(......){
data manipulation
insert(....)
}
For non-transactional storage engines, obtain a write lock on the table and issue the INSERT statements while the table is locked:
LOCK TABLES tbl_name WRITE;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
UNLOCK TABLES;
Either way, you obtain the same benefit: The index is flushed once after all the statements have been executed rather than once per INSERT statement. The latter is what happens in autocommit mode or if the table has not been locked.
For MyISAM tables, another strategy for reducing index flushing is to use the DELAYED_KEY_WRITE table option. With this option, data rows are written to the data file immediately as usual, but the key cache is flushed only occasionally rather than after each insert. To use delayed index flushing on a server-wide basis, start mysqld with the --delay-key-write option. In this case, index block writes for a table are delayed until blocks must be flushed to make room for other index values, until a FLUSH TABLES command has been executed, or until the table is closed.
If you choose to use delayed key writes for MyISAM tables, abnormal server shutdowns can cause loss of index values. This is not a fatal problem because MyISAM indexes can be repaired based on the data rows, but to make sure that the repairs happen, you should start the server with the --myisam-recover=FORCE option. This option causes the server to check MyISAM tables when it opens them and repair them automatically if necessary.
awful settings....
what about this:
can i apply this kind of lock on myISAM:
LOCK TABLES tbl_name WRITE;
for(......){
data manipulation
insert(....)
}
UNLOCK TABLES;
is it practical?
