Inserting Data - Learning MySQL and MariaDB - page 132

the server starts the insert, and then the client is locked, as well as the related tables on the
server are locked.
Delaying an INSERT
As an alternative, you can use the DELAYED option insteadof the LOW_PRIORITY op-
tion. This is deprecated in 5.6.6 of MySQL. However, if you're using an older version,
this is how you would use it:
INSERT DELAYED INTO bird_sightings
…
This is very similar to LOW_PRIORITY ; MySQL will take the request as a low-priority
one and put it on its list of tasks to perform when it has a break. The difference and ad-
vantage is that it will release the mysql client immediately so that the client can go on to
enter other SQL statements or even exit. Another advantage of this method is that multiple
INSERT DELAYED requests are batched together for block insertion when there is a gap
in server traffic, making the process potentially faster than INSERT LOW_PRIORITY .
The drawback to this choice is that the client is never informed whether the delayed inser-
tion is actually made. The client gets back error messages when the statement is entered
— the statement has to be valid before it will be queued — but it's not told of problems
that occur after the SQL statement is accepted by the server.
This brings up another drawback: delayed insertions are stored in the server's memory. So
if the MySQL daemon dies or is manually killed, the inserts are lost and the client is not
notified of the failure. You'll have to manually check the database or the server's logs to
determine whether the inserts failed. As a result, the DELAYED option is not always a
good alternative.
Raising the priority of an INSERT
The third priority option for the INSERT statementis HIGH_PRIORITY . INSERT state-
ments by default are usually given higher priority over read-only SQL statements so there
would seem to be no need for this option. However, the default of giving write statements
priority over read statements (e.g., INSERT over SELECT ) can be removed. Post-Installa-
tion touched on the configuration of MySQL and MariaDB. One of the server options that
may be set is --low-priority-updates . This will make write statements by default
a low priority statement, or at least equal to read-only SQL statements. If a server has
been set to this default setting, you can add the HIGH_PRIORITY option to an INSERT