On Wed, 9 Jun 2004 12:58:01 +0200, Jigal van Hemert <[EMAIL PROTECTED]> wrote:
Anyway, my question is this. If I do a single-statement multi-line insert,
are the auto-increment IDs of the rows inserted guaranteed to be
sequential? Bear in mind also that I'm using InnoDB tables here.
Conversely, if I know for a fact that it is not guaranteed, I know that I
need to think of something else. :-)Will locking the table work for you? If you lock the table for writing no other process can slip a query in between your queries for certain...
Regards, Jigal.
Unfortunately locking the table isn't an option as the table is being accessed extremely regularly by other clients performing similar inserts. The key to this is speed and overhead: at present I'm inserting the rows individually and recording each row's ID as I go. However I want to be able to reduce the number of queries involved, so I've though of two possibilities:
1) If the inserted rows have sequential IDs in the same order that they appeared in the INSERT statement, I can do them all in one go, use LAST_INSERT_ID to get the ID of the first and derive the rest by incrementing in the application logic.
2) Insert them all in one statement and then select them back to get each row's ID.
Now, I know that 2) will work, and it will allow me to reduce the number of queries per process from N (where N is on average about 9) to 2. However, if 1) will work it will allow me to reduce the number of queries to 1, (plus a call to LAST_INSERT_ID), and wouldn't involve having to match up the rows from the second query in 2).
If nobody knows the answer I'll just go with 2, but I thought it was an interesting bit of trivia in addition to being useful to my specific circumstance.
Thanks.
--
Russ
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
