Is it possible to use delayed inserts and retrieve the ID for follow-up queries in MySQL?

Question

I am building an analytics tool for the website and to simplify my sequence of INSERTs looks like this:

INSERT INTO visits (id, page, url) VALUES (null, 'http://...', '...');
INSERT INTO params (visit_id, name, val) VALUES (123, 'page', 'product');
INSERT INTO params (visit_id, name, val) VALUES (123, 'product_id', '7');

So in other words there is a central table and other tables that link to it using the ID of the main INSERT. I want to convert these INSERTs to DELAYED INSERTs so that users don't suffer in case the database server responsible for storing analytics data gets overloaded and can't handle all the writes anymore. However since DELAYED INSERT doesn't allow to use LAST_INSERT_ID(), I am not sure how to maintain the above relation between tables.

Is there some easy way to do it other than generating your own ID and assigning unique index to it and then using it to join tables if necessary.

Answer 1

You can use LAST_INSERT_ID() in an INSERT DELAYED statement. You just can't use LAST_INSERT_ID() to get the id for the delayed statement. Instead, it will return the value for the previous (non-delayed) insert.

This will work:

INSERT INTO visits (id, page, url) VALUES (null, 'http://...', '...');
INSERT DELAYED INTO params (visit_id, name, val) VALUES (LAST_INSERT_ID(), 'page', 'product');
INSERT DELAYED INTO params (visit_id, name, val) VALUES (LAST_INSERT_ID(), 'product_id', '7');

However, INSERT DELAYED is deprecated in the latest version of MySQL. Consider a different approach.