Big MySQL Database - Advice

[suarve]

Hi,

I have a list of 10,000 mobile phone numbers, stored in the following MySQL table:

ID
Mobile_Number
KEY

As part of a competition page I'm doing, I need to create a unique KEY for each Mobile_Number. As this is a pretty big dataset, is my below logic flawed in any way and if so, what would you recommend?

• Select * Records from a database
• Generate a unique key via PHP
• Check that key doesn't already exist
• If key is unique, insert this into the database

This would all be done within a loop.

Thanky

 

[GravyMonster]

Check that key doesn't already exist

^ There's your performance killer, although for 10k records it should still be pretty quick if you want to do it that way.

To skip this step you could append the ID field to the start/end of the key to guarantee uniqueness without having to check.

 

[Pho]

UPDATE `table` SET `key`=UUID()

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html#function_uuid

 

[GravyMonster]

^ Or that.

Never heard of it myself being a MSSQL dev.

 

[Yakyb]

thefunction in MSSql is Newid() which creates a GUID

 

[Pho]

Beat . I've been using NewID to get random data sets recently, works well .

 

[Beansprout]

For just 10k numbers MD5 should be fine!

 

[GravyMonster]

thefunction in MSSql is Newid() which creates a GUID

Learn something new everyday

 

[SoapSurgeon]

Maybe i missed something but isnt the problem that a mobile number could exist twice or more? Using Newid() would generate a different key for each of the duplicated mobile numbers, which is not what is wanted?

 

[suarve]

That uuid() would usually be fine, but the number is a kind of key that needs to be quickly typed in by attendants. Guess I'll have to just genrate this via PHP in my loop, which I imagine will be slower

 

[Dj_Jestar]

You'll need at least 5 digits for a 10k+ record table

 

[suarve]

You'll need at least 5 digits for a 10k+ record table

I remeber ages ago in A level maths we were shown how to work this out, how did you work that out?

 

[V4NT0M]

I remeber ages ago in A level maths we were shown how to work this out, how did you work that out?

Because 10,000 is 5 digits?

 

[roid]

So basically your just trying to make sure that you can't insert duplicate phone numbers? If that is the case then the only way really is to check for that phone number each time you want to insert a new one.

Or are you just trying to get the unique numbers out of the database ( 'select distinct phone_number from database' would achieve that).

 

[suarve]

Because 10,000 is 5 digits?

Nar, there's some sort of method you use calculate this.

So basically your just trying to make sure that you can't insert duplicate phone numbers? If that is the case then the only way really is to check for that phone number each time you want to insert a new one.

Or are you just trying to get the unique numbers out of the database ( 'select distinct phone_number from database' would achieve that).

All the phone numbers will be unique. I just need to make sure that the KEY is unique for every number.

 

[GravyMonster]

Nar, there's some sort of method you use calculate this.

Yeah, counting the number of digits in the maximum number to be stored

 

[Dj_Jestar]

Nar, there's some sort of method you use calculate this.




All the phone numbers will be unique. I just need to make sure that the KEY is unique for every number.

Why not use the phone number as the key?

also

ALTER TABLE `TableName` ADD UNIQUE (`mobile_number`)

 

[Dave M]

Don't worry too much about duplicates, if you use a sound random number generator you won't get many.

I'd suggest adding a unique index and just catching the error if the db spits it back at you.

 

[roid]

Just a FYI too, 10000 record isn't usually classed as a big database