7

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.

DrNoFruit
  • 185
  • 1
  • 3
  • 12

4 Answers4

30

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

alexchet
  • 479
  • 1
  • 5
  • 14
11

If you don't need that column because you have another identifier for a record which is unique. Like a supplied measurement_id or whatever or even a combined key: measurement_id + location_id it should not be needed to use an auto increment key. If there is any chance you won't have a unique key than make one for sure.

What if I need a very very big autoincrement ID?

Are you really sure you have so many inserts and deletes you will get to the limit?

Community
  • 1
  • 1
Luc Franken
  • 2,994
  • 1
  • 17
  • 14
  • Yeah, we're already nearly at the 500 million mark for the auto increment id. The column holds keywords associated with file data. 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 and file id fields combined. – DrNoFruit Jul 21 '12 at 10:48
  • Did you read about the difference of signed and unsigned? 500 million is a lot but not a limit. http://ronaldbradford.com/blog/bigint-v-int-is-there-a-big-deal-2008-07-18/ BIGINT[(M)] [UNSIGNED] [ZEROFILL] A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. – Luc Franken Jul 21 '12 at 10:54
  • Yep, know all about unsigned. Thing is, even with such a massive number, assuming the application runs for a few years (especially if usage levels increase even further) it will definitely reach that number at some point. What then? – DrNoFruit Jul 21 '12 at 11:03
  • @DrNoFruit: did you read the link posted by Luc Franken? it seems extremely unlikely that you could reach the limit so fast, unless you insert thousands of billions of rows per second, every second! – Jocelyn Jul 21 '12 at 11:06
  • Thanks for your thoughts, all very useful. It does seem unlikely Jocelyn, I agree... but if we assume that the upper limit will be reached at some point, I want to think about how to handle that situation now rather than later. Is it literally just going to be a question of reseeding the auto_increment number after recreating the table? Or would there be a better way of doing things? – DrNoFruit Jul 21 '12 at 11:48
  • Oh, and yes, I did read the link! But the author says: "There are exceptions to this rule, if you do a huge number of inserts and deletes, then while you may not have 2.1 Billion rows, you may have done 2.1 Billion inserts. Again better design practices should be considered in this case." What would these better practices be? – DrNoFruit Jul 21 '12 at 11:50
  • 1
    Yes theoretically you are right, but do: 18446744073709551615 divided by 500.000.000 and see how many times you can do the amount of inserts you have done until now. You won't get there soon, really. – Luc Franken Jul 21 '12 at 12:11
  • Fair point. Well I'll trundle on as I have done up until now then. Thanks for your thoughts, much appreciated! – DrNoFruit Jul 21 '12 at 12:32
  • You're welcome! Tell us if you reach the limit! There may be some trick to solve that too! – Luc Franken Jul 21 '12 at 13:16
9

If we inserted 1 hundred thousand (100,000) records per second into the table then it would take 2,924,712 yrs

If we inserted 1 million (1,000,000) records per second into the table then it would take 292,471 yrs

If we inserted 10 million (10,000,000) records per second into the table then it would take 29,247 yrs

If we inserted 100 million records per second into the table then it would take 2,925 yrs

If we inserted 1000 million records per second into the table then it would take 292 yrs

So don't worry about it

hakki
  • 6,181
  • 6
  • 62
  • 106
  • not even google process at 1000 Million rows per second. – Jones G Aug 11 '20 at 01:11
  • 1
    I worried about the same thing. I came looking for a answer. Now I feel like I will definitely not gona stay alive to to see my pk run out. If It did run out . That's that developers problem – Avinash Reddy Sep 30 '21 at 16:54
0

maybe it's bit too late, but you can add trigger in DELETE,

here is sample code in SQL SERVER

CREATE TRIGGER resetidentity
    ON dbo.[table_name]
    FOR DELETE
AS
    DECLARE @MaxID INT
    SELECT @MaxID = ISNULL(MAX(ID),0)
    FROM dbo.[table_name]
    DBCC CHECKIDENT('table_name', RESEED, @MaxID)
GO

In a nutshell, this will reset you ID (in case it is auto increment and primary). Ex: if you have 800 rows and deleted last 400 of them, next time you insert, it will start at 401 instead of 801.

But the down is it will not rearrange your ID if you delete it on the middle record.EX if you have 800 rows and deleted ID 200-400, ID still count at 801 next time you write new row(s)

KokoriNut
  • 175
  • 1
  • 3
  • 11