Menu
I have a table that is exposed to large inserts and deletes on a regular basis (and because of this there are large gaps in the number sequence of the primary id column). It had a primary id column of type 'int' that was changed to 'bigint'. Despite this change, the limit of this datatype will also inevitably be exceeded at some point in the future (current usage would indicate this to be the case within the next year or so).
How do you handle this scenario? I'm wondering (shock horror) whether I even need the primary key column as it's not used in any obvious way in any queries or referenced by other tables etc. Would removing the column be a solution? Or would that sort of action see you expelled from the mysql community in disgust?!
We're already nearly at the 500 million mark for the auto increment id. The table holds keywords associated with file data in a separate table. Each file data row could have as many as 30 keywords associated with it in the keywords table, so they really start to stack up after you've got tens of thousands of files constantly being inserted and deleted. Currently the keyword table contains the keyword and the id of the file it's associated with, so if I got rid of the current primary id column, there would be no unique identifier other than the keyword (varchar) and file id (int) fields combined, which would be a terrible primary key.
All thoughts/answers/experiences/solutions very gratefully received.
280
You can use BIGINT as a primary key but with some penalties. BIGINT takes up more space on disk storage than INT and using BIGINT as a primary key (or any index) will add size to the index, perhaps as much as doubling it. This can have a performance impact on searching the index and make it slower to run queries.
The BIGINT data type is an integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. BIGINT is SQL Server's largest integer data type. It uses 8 bytes of storage. BIGINT should be used when values can exceed the range of INT.
A large integer. The signed range is -9223372036854775808 to 9223372036854775807 . The unsigned range is 0 to 18446744073709551615 . If a column has been set to ZEROFILL, all values will be prepended by zeros so that the BIGINT value contains a number of M digits.
I know it has been already answered a year ago but just to continue on Luc Franken answer,
If you insert 500 million rows per second, it would take around 1173 years to reach the limit of the BIG INT. So yeah i think don't worry about that
141
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
