Dealing with large amount of data in php/mysql

So i 开发者_开发知识库have something like a auction and for each deal or that auction i have to generate random identifier code and assign to user. So i came up with something like this for db storage {1:XKF3325A|ADSTD2351;7:ZARASR23;12:3290OASJX} - so what i have there is user id : random code and some user can have several random codes seperated by |.

My question is what type of storing i should use for my db? The codes i generate for users might be over 2k-3k.

Perhaps you're asking the wrong question. You simply shouldn't be dealing with identifiers of this size. The answer is to deal with smaller identifiers by using a better identifier generation algorithm. Your table indexes will thank you.

So the question you should be asking is: How do I create short but unique identifiers? The answer is as follows:

You should use a cryptographically secure hash algorithm (eg. SHA-1) to generate your identifier, or just use a UUID implementation to do this. PHP has a UUID implementation called uniqid (might have to be compiled in), so there's really no need to roll your own. Both methods give you an ID that is way shorter than what you're using, and both can "guarantee" uniqueness across a huge sample size (and more effectively than your algorithm). And when I say shorter, I'm talking anywhere between 16-64 bytes at most (SHA-1 generates 40byte hashes).

If you go the SHA-1 hash route, the methodology would be to hash some random (but unique-to-the-user input), like sha1(timestamp+username+itemname+randomseed). You can also make use of uniqid here (see the comments in the PHP documentation for the function) and do: sha1(uniqid()). Make sure to read the notes about uniqid() to see the caveats about generating many ids per second.

So what if they are over 3k? MySQL can deal with huge stuff, don't worry. Just use two tables, like:

Users
 - id
 - other info..

Identifiers
 - random_str
 - foreign key to user

Then you can fetch identifiers for an id, fetch the user that an identifier belongs to, etc

First - see Loren Segal's answer for some good ideas.

Second - why would you use anything random to ensure uniqueness? Random values don't guarantee uniquness -- even if they can make it very unlikely that you'll have a collision.

In most every case, relational databases can solve your problem without any tricks. Indexes and composite keys are designed to solve this kind of problem efficiently.

If you need a single value to uniquely identify something, look into the uniquid stuff that Loren mentioned.