Is there a reason not to use auto_increment for an index on a database table?

Question

I have inherited the task of maintaining a very poorly coded e-commerce site, and I am working on refactoring a lot of code and trying to fix current errors.

Each database insert (adding an item to the cart, etc.) starts with the grab_new_id function, which counts the number of rows in the table, and then, starting from this number, queries the database to find the unused index number. In addition to being horrible in performance (there are already 40,000+ rows and indexes are regularly deleted, so it sometimes takes a few seconds to find a new identifier), this is interrupted regularly when two operations are performed at the same time, as two records are added with duplicate identifier numbers.

It seems idiotic to me - why not just use auto-increment in the index field? I tested it in both directions and added rows to the table without specifying an index identifier (obviously) many times faster. My question is: can anyone think of any reason that the original programmer could do? Is there some kind of school of thought where auto_increment is somehow considered a bad form? Are there databases that do not have the ability to grow automatically?

Answer 1

I saw this before from someone who did not know that this function exists. Definitely use the auto zoom feature.

Some people use the “collapse their own” approach to everything, often because they did not take the time to find out if this feature is available or if someone else came up with it. You will often see crazy workarounds or bad / fragile code from these people. Inheriting a bad database is not at all fun, good luck!

Answer 2

Well, Oracle has sequences, but not auto-generated identifiers, as I understand it. However, usually these things are done by developers who don’t understand database programming, and who hate to see data gaps (as you get from rollbacks). There are also people who like to create an identifier, so they are available for use on child tables, but most databases with auto-generated identifiers also have the ability to return that identifier to the user at creation time.

Answer 3

The only problem that I found partially reasonable (but completely avoided!) Regarding auto_inc fields is that by default, some backup tools include auto_inc values ​​in the table definition, even if you don't include data in a db dump, which may be uncomfortable.

Answer 4

Depending on the specific situation, there are many reasons not to use consecutive numbers as the primary key.

However, provided that I want consecutive numbers to be the primary key, I see no reason not to use the built-in auto_increment function MySQL offers

Answer 5

They may just be inexperienced and unfamiliar with auto-increment. One of the reasons that I can think of, but does not necessarily make sense, is that it is difficult (not impossible) to copy data from one environment to another using auto-increment identifiers.

For this reason, I used consecutive guides as my main key before for the convenience of data transfer, but counting the lines to populate the identifier is a bit of WTF .

Answer 6

This was probably done for historical reasons; that is, earlier versions did not have variable auto-breeding. I wrote code that uses manual auto-padding fields in databases that don't support auto-padding types, but my code was not as inefficient as pulling count ().

One of the problems with using autoinc fields as the primary key is that moving records to and from tables can cause the primary key to change. Therefore, I would recommend developing a front in the "LegacyID" field that can be used as a future storage for the primary key at the moments when you move records inside and outside the table.

Answer 7

Two things to watch:

1. Your RDBMS intelligently sets the auto increment value on restart. Our engineers deployed their own auto-increment key to bypass the auto-incrementing field, jumping an order of 100,000 each time the server restarts. However, at some point, Sybase added the ability to set the auto-increment size.

2. Another place where auto-increment can become unpleasant is database replication and the use of a wizard configuration. If you write in both databases (NOT ADVISED), you may encounter collision identification.

I doubt it is, but all you need to know.

Answer 8

I could see if the identifiers were created on the client and entered into the database, this is a common practice when speed is required, but what you described seems to be above and unnecessary. Delete it and run the auto-increment id.