6

This is probably a common situation, but I couldn't find a specific answer on SO or Google.

I have a large table (>10 million rows) of friend relationships on a MySQL database that is very important and needs to be maintained such that there are no duplicate rows. The table stores the user's uids. The SQL for the table is:

CREATE TABLE possiblefriends(
 id INT NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY(id),
 user INT, 
 possiblefriend INT)

The way the table works is that each user has around 1000 or so "possible friends" that are discovered and need to be stored, but duplicate "possible friends" need to be avoided.

The problem is, due to the design of the program, over the course of a day, I need to add 1 million rows or more to the table that may or not be duplicate row entries. The simple answer would seem to be to check each row to see if it is a duplicate, and if not, then insert it into the table. But this technique will probably get very slow as the table size increases to 100 million rows, 1 billion rows or higher (which I expect it to soon).

What is the best (i.e. fastest) way to maintain this unique table?

I don't need to have a table with only unique values always on hand. I just need it once-a-day for batch jobs. In this case, should I create a separate table that just inserts all the possible rows (containing duplicate rows and all), and then at the end of the day, create a second table that calculates all the unique rows in the first table?

If not, what is the best way for this table long-term?

(If indexes are the best long-term solution, please tell me which indexes to use)

Community
  • 1
  • 1
eric
  • 1,453
  • 2
  • 20
  • 32
  • question, do u need query to table `possiblefriends`? i just thinking u probably can split the table as per user, it will benefits when u query, however it might turn into a maintenance disaster in long run – ajreal Nov 11 '10 at 09:12
  • @ajreal: do you mean each user has his own table? there would be close to a million users or so, so that would probably make things very complicated. – eric Nov 11 '10 at 10:04
  • yes, that's y i mentioned it might turn into a maintenance disaster, how about use 1k user per table or so? imagine u put all data into one table, and happening the table crashed and unrecoverable, or even is recoverable, how long u can bear with down time? – ajreal Nov 11 '10 at 12:48

2 Answers2

8

Add a unique index on (user, possiblefriend) then use one of:

to ensure that you don't get errors when you try to insert a duplicate row.

You might also want to consider if you can drop your auto-incrementing primary key and use (user, possiblefriend) as the primary key. This will decrease the size of your table and also the primary key will function as the index, saving you from having to create an extra index.

See also:

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    i've read that question. Are INSERT IGNORE or INSERT...ON DUPLICATE KEY UPDATE efficient for a table with 100s of millions of rows in general? – eric Nov 11 '10 at 08:33
  • 1
    @eric: I'd imagine that `INSERT IGNORE` is the fastest, but I'm just guessing. To be sure you could run a performance test on all three methods. The top-voted answer to the question I linked to recommends using `INSERT...ON DUPLICATE KEY UPDATE`. – Mark Byers Nov 11 '10 at 08:36
  • @symcbean: Thanks for pointing that out. Somehow I forgot to mention this very important detail! – Mark Byers Nov 11 '10 at 12:37
2

A unique index will let you be sure that the field is indeed unique, you can add a unique index like so:

CREATE TABLE possiblefriends( 
 id INT NOT NULL AUTO_INCREMENT,  
 PRIMARY KEY(id), 
 user INT,  
 possiblefriend INT,
PRIMARY KEY (id),
UNIQUE INDEX DefUserID_UNIQUE (user ASC, possiblefriend ASC))

This will also speec up your table access significantly.

Your other issue with the mass insert is a little more tricky, you could use the in-built ON DUPLICATE KEY UPDATE function below:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;
JonVD
  • 4,258
  • 24
  • 24
  • thanks. is it always better to use an index? would there be any cost to using an index for larger tables that i should consider? – eric Nov 11 '10 at 08:47