MySQL DELAYED INSERT and MySQL DELAYED UPDATE

Overview

Performing MySQL queries on your websites or server can cause long delays in a page loading or script executing while the MySQL INSERT or MySQL UPDATE finishes executing. This causes long delays for the user or possibly delays the rest of a script from running until the MySQL query completes (or errors out). Generally the delay on mysql inserts or update is minimal however on large complex queries with lots of join across multiple tables and millions of rows of data (or badly indexed tables), there will be a significant pause while the query completes.

Solution to Long Queries

If there is no need for the user to see the result of the query, such as queries that are involved with logging statistics or building hidden reports, then it would be highly recommended to use mysql delayed insert or mysql delayed update syntax. Using MySQL DELAYED INSERT and MySQL DELAYED UPDATE to send the queries into the queue and run in the background allowing the page or script to continue executing. It is simple to use and can be implemented by adding the `delayed` keyword after INSERT or UPDATE command and the sql query will be queued by the MySQL server to be run in the background and the page while continue loading while the sql query is executed in the background as the example below shows:

MySQL DELAYED INSERT

MySQL Insert statements can be run in the background by using the following syntax:

INSERT DELAYED `some massive time consuming query`;

MySQL DELAYED UPDATE

MySQL Update statements can be run in the background by using the following syntax:

UPDATE DELAYED `some massive time consuming query`;

Conclusion

Now the page will continue to load while the MySQL server continues to execute the query which can drastically speed up page loading speeds or script execution times. Bear in mind that if the user needs to see the result, then you won’t be able to use mysql delayed inserts.