0

Guys I have beginners mysql/php question.

I'm having 3 services that "collaborate" for UUID generation. Service3 receives unique requests from unique users, and gives them unique IDs(UUID) from DB. Users represent their companies, and I can't allow by any chance two IDs in one company.Service2 must know when the level of free UUID in DB is too low. Then, I am having Service1 (Node.js) which takes request from Service2 and gives a package of UUIDs(JSON) as a response. Then Service2 saves them to database if the level of UUIDs in DB is too low (too low means, the level equals to incoming UUID package, same number of UUIDs).

UUIDs are in one table and the requests become too slow when there are millions of UUIDs. How can I prevent duplicates/collisions efficiently when I'm working with bigger numbers. Hope I was clear.

EDIT: I made it unique but, when I run "SELECT ..." to check if there are duplicates, it takes too long to check millions of rows.

Hrvoje Antunović
  • 781
  • 5
  • 15
  • Make the id `unique` in the table and the RDBMS will prevent the insertion of a duplicate and give you an error, right? – developerwjk Dec 07 '15 at 21:16
  • 1
    The [probability of a collision](https://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates) per 70 *trillion* UUIDs is 0.0000000005. Don't worry about it. Really. From the MySQL docs: "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." See this answer for more information: http://stackoverflow.com/questions/9679860/mysql-uuid-when-not-unique – Jordan Running Dec 07 '15 at 21:16
  • 1
    ^ "Don't worry about it" means: Set the field as `unique` and then don't worry about it... – developerwjk Dec 07 '15 at 21:17
  • 1
    Most probably I'll take that path of "not worrying about it" but I would be happy if there is some programmatical prevention. :D Thanks – Hrvoje Antunović Dec 07 '15 at 21:24
  • When using proper UUIDs chances are much bigger that a meteoroid will hit your server. Will you build some programmatical prevention for that? – Sander Toonen Dec 07 '15 at 21:29

1 Answers1

1

I made it unique but, when I run "SELECT ..." to check if there are duplicates, it takes too long to check millions of rows.

set index on it

kamil_oos
  • 174
  • 2