Mysql – What to do when a field in a table approaches the max signed or unsigned 32 bit integer

MySQL

In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)… what to do when the time comes and it approaches the max signed or unsigned number of the current datatype? (A 32-bit INT)? I'm guessing that the database server would overflow when it tries to assign the (2∧32)-1 number to the next entry, so, how to avoid that happening (without changing the datatype, for the sake of the question) and keep adding records? What would you do?

Why would I use INT's and not, for example, VARCHARS?

It has been several days since I've asked myself this hypothetical question and I would like to know what a professional would do.

Best Answer

You would generally use integers rather than varchars because they consume less space, have well understood sorting pattern are fast to index etc. Integers are natural data types of a CPU, and hence performance is generally optimal. Typically an integer is 4 bytes, equivalent to just 4 characters in a (non-unicode) varchar.

If you were worried about running out of space with an INT type, then try BIGINT, which gives you 8-byte numbers. The limit on this is pretty huge, and you'd probably run out of disk space before you reached that limit of records :-) The performance of BIGINT is also going to be very good, especially as many servers are now 64-bit too.

The answer to the first part of your question about what happens when you run out in INTs is not simple, especially as you said without changing the datatype to BIGINT. Basically there isn't much you can do, and what you may be able to do is limited very much by the nature of the data in your database. What records are foreign-keyed to this data? Do you still need all the data in that table and the related records? On the assumption that you could archive off a lot of the initial data (and its related data), then the only thing I can suggest is moving the data out of the table (lets say the first 1 to X million records), and then resetting the identity seed to 1. There are all sorts of reasons though I wouldn't recommend it - for example there are many bits of code I have seen that do things like check the maximum value of an id field, to see what has just been added, and that would not work (and shouldn't be done). Also, people assume that record N was created before N+1. No easy answer I think.

Finally, I don't know about MySQL, but SQL Server would give an overflow error if you reached the limit.

Related Solutions

Mysql – Optimizing User Defined Custom Columns

A lot of people want to do what you are describing. They get into big trouble when the dataset gets big.

All of the custom fields need to be searchable.

Only one solution: MariaDB with "Dynamic Columns". For discussion of why it is impractical otherwise, and discussion of lesser solutions: http://mysql.rjweb.org/doc.php/eav

an auto increment primary key ID along with an app generated uuid key which will be indexed

Once the table (or at least the index) is too big to be cached, UUID/GUIDs lead to lots of random disk I/O and destroy the cache. Further discussion (and a workaround): http://mysql.rjweb.org/doc.php/uuid

Mysql – Search multiple values in multiple columns

Personally I would create your table

id
result_id
value

But with a single value in the value column. Now you can index on the value column and that should dramatically speed things up. Your query would then look like this:

SELECT result_id
FROM TableName
WHERE VALUE IN ('02','12','20')
GROUP BY result_id
HAVING COUNT(1) >= 3

One of the benefits of this is that you can now easily check for 1 match, 2 matches etc. You can also increase the number of items they could check to 50 and your table structure wouldn't have to change.

That being said right now that won't work for you. Right now I would use something like this:

SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)

Best Answer

Related Solutions

Mysql – Optimizing User Defined Custom Columns

Mysql – Search multiple values in multiple columns

Related Question