INSERT DELAYED ...
DELAYED option for the
statement is a MariaDB/MySQL extension to standard SQL that is very useful if you have
clients that cannot or need not wait for the
complete. This is a common situation when you use MariaDB for logging and you
also periodically run
statements that take a long time to complete.
When a client uses
INSERT DELAYED, it gets an okay from the
server at once, and the row is queued to be inserted when the table is not in
use by any other thread.
Another major benefit of using
INSERT DELAYED is that
inserts from many clients are bundled together and written in one block. This
is much faster than performing many separate inserts.
INSERT DELAYED is slower than a normal
INSERT if the table is not otherwise in use. There is also
the additional overhead for the server to handle a separate thread for each
table for which there are delayed rows. This means that you should use
INSERT DELAYED only when you are really sure that you need
The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.
The number of concurrent
INSERT DELAYED threads is limited by the
max_delayed_threads server system variables. If it is set to 0,
INSERT DELAYED is disabled. The session value can be equal to the global value, or 0 to disable this statement for the current session. If this limit has been reached, the
DELAYED clause will be silently ignore for subsequent statements (no error will be produced).
There are some constraints on the use of
INSERT DELAYEDworks only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. If you execute INSERT DELAYED with another storage engine, you will get an error like this:
ERROR 1616 (HY000): DELAYED option not supported for table 'tab_name'
- For MyISAM tables, if there are no free blocks in the middle of the data
file, concurrent SELECT and INSERT statements are supported. Under these
circumstances, you very seldom need to use
INSERT DELAYEDwith MyISAM.
INSERT DELAYEDshould be used only for
INSERTstatements that specify value lists. The server ignores
INSERT ... SELECTor
INSERT ... ON DUPLICATE KEY UPDATEstatements.
- Because the
INSERT DELAYEDstatement returns immediately, before the rows are inserted, you cannot use
LAST_INSERT_ID()to get the
AUTO_INCREMENTvalue that the statement might generate.
DELAYEDrows are not visible to
SELECTstatements until they actually have been inserted.
INSERT DELAYED, ROW_COUNT() returns the number of the rows you tried to insert, not the number of the successful writes.
DELAYEDis ignored on slave replication servers, so that
INSERT DELAYEDis treated as a normal
INSERTon slaves. This is because
DELAYEDcould cause the slave to have different data than the master.
INSERT DELAYEDstatements are not safe for replication.
INSERT DELAYEDstatements are lost if a table is write locked and ALTER TABLE is used to modify the table structure.
INSERT DELAYEDis not supported for views. If you try, you will get an error like this:
ERROR 1347 (HY000): 'view_name' is not BASE TABLE
INSERT DELAYEDis not supported for partitioned tables.
INSERT DELAYEDis not supported within stored programs.