--- title: MySQL Auto Incremented Tables and Upserts description: "A recipe for wasted primary keys: Why using upserts in an auto incremented table with a unique UUID field in MySQL (a best practice) is an unexpected mistake." --- # MySQL Auto Incremented Tables and Upserts By [Blue Matador](https://www.bluematador.com/blog/author/blue-matador) on September 18, 2017 #### ![mysql-auto-incremented-tables-and-upserts](https://www.bluematador.com/hs-fs/hubfs/old-assets/old-theme/Images/mysql-auto-incremented-tables-and-upserts.png?width=1441&name=mysql-auto-incremented-tables-and-upserts.png) According to best practices, when modeling a MySQL relation that has a UUID as its id, you should still create a hidden numeric `auto_increment` field for the primary key. Doing so has all sorts of performance advantages. However, if you’re used to writing upsert queries with MySQL’s `ON DUPLICATE KEY UPDATE...` syntax, you’ll soon find that doing so is a recipe for wasting auto increment values. For example, consider the following create table statement: ``` CREATE TABLE contrived_examples( hidden_id BIGINT AUTO_INCREMENT NOT NULL, id BINARY(16) NOT NULL UNIQUE, updateable INT NOT NULL, PRIMARY KEY (hidden_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` Performing an upsert on the table will consume an auto increment value each time, regardless of whether the query only updates a row. ``` INSERT INTO contrived_examples (id, updateable) VALUES (0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, 42) ON DUPLICATE KEY UPDATE updateable=VALUES(updateable); ``` In the insert case, a record with the specific UUID doesn’t exist, and the query auto increments `hidden_id` as you would expect. However, when you trigger the duplicate key part of the query during an update, an auto increment value is also consumed because MySQL first tries to perform the update (consuming an id), detects the duplicate, and then performs the update. This is a simple oversight that is easy to make, but left unchecked, can waste much of your id space. It should also be noted that the same problem can be experienced when using `INSERT IGNORE...`. To avoid either type of query, you should do the following: 1. Try to insert the record (`INSERT INTO contrived_examples (id, updateable) VALUES (0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, 42)`) 2. Check/catch the case that no rows were inserted 3. If no rows were inserted, update the existing row (`UPDATE contrived_examples SET updateable=42 WHERE id=0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`) This issue is why I spent time changing all our fancy upserts into separate update and insert queries shortly after our [cloud monitoring service](https://www.bluematador.com/), Blue Matador, was released. We monitor servers and applications all day every day and it didn’t take long for us to notice a very large gap in the ids that were being generated. Hopefully this post helps you avoid the same level of tedium. --- Monitor your MySQL databases with Watchdog, the forever free server monitor from Blue Matador. ### What to Read Next ### Subscribe to get blog updates in your inbox. **Get started with alert automation **Proactive, automated monitoring [![START FREE TRIAL](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/3917309/d02ccf41-b876-4856-b1aa-2121845ea2f6.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/3917309/d02ccf41-b876-4856-b1aa-2121845ea2f6) **MONITORING** [AWS monitoring](https://www.bluematador.com/aws-monitoring) [Kubernetes monitoring](https://www.bluematador.com/kubernetes-monitoring) [Serverless monitoring](https://www.bluematador.com/serverless) [Azure monitoring](https://www.bluematador.com/azure-monitoring) [Windows / Linux monitoring](https://www.bluematador.com/server-monitoring) **WHY BLUE MATADOR** [Pricing](https://www.bluematador.com/pricing) [How it works](https://www.bluematador.com/how-it-works) [Competitors](https://www.bluematador.com/l/blue-matador-vs-competitors) [Customers](https://www.bluematador.com/customers) **RESOURCES** [Blog](https://www.bluematador.com/blog) [eBooks](https://www.bluematador.com/ebooks)        [Kubernetes ](https://www.bluematador.com/ebook/kubernetes-for-startups)        [Cloudwatch](https://www.bluematador.com/ebook/amazon-cloudwatch-monitoring-guide) [Docs](https://www.bluematador.com/docs) [Integrations](https://www.bluematador.com/integrations) [Developers](https://developers.bluematador.com/) **COMPANY** [Contact](https://www.bluematador.com/contact) [**SUPPORT**](https://help.bluematador.com) ![bluematador-logo-horizontal-reversed-white-footer](https://www.bluematador.com/hubfs/2020/footer/bluematador-logo-horizontal-reversed-white-footer.svg "bluematador-logo-horizontal-reversed-white-footer") [![START FREE](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/3917309/ccdd6e2d-6352-4c30-9f68-d22b43975967.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/3917309/ccdd6e2d-6352-4c30-9f68-d22b43975967) [![REQUEST DEMO](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/3917309/045e18fc-dcbc-4e62-aef2-abe3df55b6d6.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/3917309/045e18fc-dcbc-4e62-aef2-abe3df55b6d6) © Blue Matador, Inc. All Rights Reserved.[Terms & Conditions](https://www.bluematador.com/terms-and-conditions)[Privacy Policy](https://www.bluematador.com/privacy-policy) [![BM-Facebook](https://www.bluematador.com/hubfs/2020/footer/bm-facebook-logo.svg)](https://www.facebook.com/bluematadorinc/) [![BM-LinkedIn](https://www.bluematador.com/hubfs/2020/footer/bm-linkedin-logo.svg)](https://www.linkedin.com/company/blue-matador-inc) [![BM-Twitter](https://www.bluematador.com/hubfs/2020/footer/bm-twitter-logo.svg)](https://twitter.com/bluematadorinc) [![BM-YouTube](https://www.bluematador.com/hubfs/2020/footer/bm-youtube-logo.svg)](https://www.youtube.com/channel/UCjy2_6-CjS6E-0kz_LYNzLg) Please enable JavaScript to view the [comments powered by Disqus.](https://disqus.com/?ref_noscript)