MySQL and LAST_INSERT_ID()

We've recently migrated our software servers from a dedicated server environment (BlueGecko - really good people and service) to Amazon's EC2 'virtual compute cloud' environment.

The new system has a very nice performance monitoring capability based on Ganglia that gives us visibility into the performance of service requests as well as details on more fine-grained functions that take place within each request. We can now see not only the number of requests or functions but also the average duration and the time it took for 50%, 95% or 99% of the requests to complete in a five minute interval. This percentile breakdown gives a quick feel for how 'spiky' performance is and how common outliers are.

So far, things have gone very well but there was one function of the system that seemed to suddenly have terrible performance. I was able to quickly see which area of the code was involved, which pointed me to some SQL statements.

As our system adds new anonymous profiles, the code retrieves the unique number assigned by MySQL using the special query that MySQL provides:

SELECT LAST_INSERT_ID() AS user_id FROM oo_anon_user

However, it turns out that this SQL is actually incorrect - the extra "FROM oo_anon_user" caused the database to return every single record from the table back to the client, on every request that created a new record. This of course took some time.

The correct syntax to retrieve an auto-increment field from a MySQL table is

SELECT LAST_INSERT_ID() AS user_id

Remember to omit any FROM clause.

It's much much much faster now.