Randomizing the Auto Increment in Mysql

Question

We have a database, where we insert data into table whenever we receive an order from our services.

We use auto_increment to generate the ID and assign the order with the same. This is usually same everywhere.

But the problem is, since this information is passed to third party vendors, they are able to trace, how many orders we are receiving per day. By sending a request in the early morning and sending another request in midnight and count the difference in the ID.

This is giving insight information of our services. Since it's bit complex system, we can't do complex operation that can randomise this.

My initial idea is to generate auto_increment numbers in bit random fashion way.

1,2,3,4,5,67,68,69,70,71,133,134,135,136,137,189,190,191,192,193,194

I guess it should be simple, as Mysql runs out of cache, generate next sequence by appending some random number.

How to achieve this without impacting the performance?

• ypercubeᵀᴹ: What information are you passing exactly? Only the IDs? If these vendors can send arbitrary queries to the database, they can always run SELECT * FROM Orders; or SELECT COUNT(*) FROM Orders WHERE Date >= CURRENT_DATE(); and get this info.
• Abdul Manaf: May be you can try using UUID instead od Auto_Increment.
• RaceBase: @ypercube, they don't have access to database. I will send the order confirmation with the generated id. Since we do operations over 0.1 million every day, we can't use UUID too as it can be quite huge and our customer care will not be able to answer
• ypercubeᵀᴹ: I see. So any of these vendors, can get a an idea about the size of the orders of a day by the IDs of their orders only. I think that adding a UUID column (and hiding the actual ID from vendors) might be your best option. The customer care will still be able to check things using a probably altered query (so by the UUID and not the ID). This will need only a new column and an index on it. And (I suppose you use InnoDB) you'll want to keep the clustered index on ID and all the other table's FKs untouched.

Answer 1

Here is an overview of methods to generate random unique ids in mysql, by Rick James: http://forums.mysql.com/read.php?24,425424,425491

The overview is quite comprehensive, gives 5 different strategies, lists pros and cons for every one.

Answer 2

It would be better to add a new field in the database. Something like "order_number" and have that be a random number generated between x and y. That way, one day a client might see an order number of 1000, next day it might be 500, following day 9038439. It is the safest way to hide your sales numbers and what I've seen most popular software solutions implement (e.g. WHMCS).

However, if you want to randomize your auto increment, set up a script that runs every 5 minutes, or every hour, or better yet, only once after a new order is inserted in the database. The script should:

1. Find out what the current auto increment value is
2. Increment it by a random amount

This way you will have order numbers going:

1,5,6,20,25,100,190,201,202,290,300 etc

To get the current auto increment value for a table:

SHOW TABLE STATUS FROM `myDatabase` WHERE `name` LIKE 'myTable' ;

To update it:

ALTER TABLE myTable AUTO_INCREMENT = 901111

p.s. I found this question because I was looking for a way to do this: ALTER TABLE myTable AUTO_INCREMENT = 901111 + rand(). But that doesn't work. When I figure out how to do that, I'll update this answer as it will certainly make it easier for you too. In fact, I'm hoping I can combine the above commands into one, so that I can query the current Auto Increment value and also increase it by a random amount, with a single mysql query.