SQL DELAYED

What is SQL DELAYED?

A MySQL-specific modifier that queues INSERT or LOAD DATA operations for asynchronous execution, returning control to the client immediately.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DELAYED: Supported: MySQL ≤ 5.7 (default disabled), MariaDB Not supported: MySQL 8.0+, PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, Redshift

SQL DELAYED Full Explanation

DELAYED is a legacy MySQL keyword that can appear after INSERT or LOAD DATA statements. When used, the server does not write the row(s) immediately. Instead, it places them in a queue handled by a dedicated thread that performs the insert when the table is not locked by other sessions. The client receives an OK packet at once, so the calling application continues without waiting for disk I/O. Only MyISAM, ARCHIVE, and MEMORY tables ever honored DELAYED; it never worked with InnoDB. MySQL 5.6 marked the feature deprecated, and MySQL 5.7 disabled it by default. It was fully removed in MySQL 8.0. MariaDB still supports it. Because rows are written later, LAST_INSERT_ID() and ROW_COUNT() values reported to the client reflect the queued write, not the final storage result. If the server shuts down before the delayed thread flushes its queue, those rows are lost.

SQL DELAYED Syntax

INSERT DELAYED INTO table_name (column_list)
VALUES (value_list);

LOAD DATA DELAYED INFILE 'file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',';

SQL DELAYED Parameters

Example Queries Using SQL DELAYED

-- Log events without blocking the application
INSERT DELAYED INTO event_log (event_type, created_at)
VALUES ('SERVER_RESTART', NOW());

-- Bulk import with minimal locking
LOAD DATA DELAYED INFILE '/tmp/big_import.csv'
INTO TABLE staging_raw
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Expected Output Using SQL DELAYED

The client instantly receives an OK status
Rows are placed in a queue and are later inserted by the DELAYED handler thread when the table becomes idle

Use Cases with SQL DELAYED

High-throughput logging where occasional loss is acceptable
Large batch imports that should not block reads or writes on busy MyISAM tables
Situations needing low client latency more than immediate durability

Common Mistakes with SQL DELAYED

Trying DELAYED with InnoDB tables (never supported)
Using DELAYED in MySQL 8.0 or later where the keyword produces a syntax error
Expecting guaranteed durability; queued rows can be lost on crash or shutdown
Assuming triggers, foreign keys, or ON DUPLICATE KEY UPDATE clauses will fire; they do not work with DELAYED

First Introduced In

MySQL 3.22 (MyISAM engine)

Frequently Asked Questions

What is the purpose of INSERT DELAYED?

It lets the client return immediately by queuing rows for background insertion, reducing perceived latency on write-heavy MyISAM tables.

Why was DELAYED removed from MySQL?

Modern storage engines like InnoDB ignore DELAYED, and the feature risks data loss. MySQL deprecated it in 5.6 and removed it in 8.0 to simplify the codebase.

Can I use DELAYED with transactional tables?

No. InnoDB, the default transactional engine, never supported DELAYED. Use standard INSERT with proper indexing or buffering strategies instead.

What is a safe alternative to DELAYED for high-volume logging?

Consider batching inserts in the application, using INSERT...VALUES lists, or writing to a message queue (Kafka, RabbitMQ) that later loads data into MySQL.