Most efficient way to set up a database handling millions of links? I'm looking at making a site that will potentially have up to a million users, and each user will have hundreds/thousands of links connected to a set of sites, as well as other info. I'm using mysql and was thinking of setting up the database with tables for each site in the set along with tables for the other info...

Table 1

-----------------------------
|UserID  |  Links for site 1|
-----------------------------
  512    |  www.site1.com/4564
  512    |  www.site1.com/1298
  512    |  www.site1.com/09312
  512    |  www.site1.com/126543
  512    |  www.site1.com/231
  6143   |  www.site1.com/32
  6143   |  www.site1.com/566478


Table 2

-----------------------------
|UserID  |  Links for site 2|
-----------------------------
  512    |  www.site2.com/click/45564
  512    |  www.site2.com/click/898
  512    |  www.site2.com/click/7312
  512    |  www.site2.com/click/56543
  512    |  www.site2.com/click/4331
  6143   |  www.site2.com/click/9832
  6143   |  www.site2.com/click/78


.......

Table 20

-----------------------------
|UserID  | Links for site 20|
-----------------------------
  512    |  www.site20.com/564
  512    |  www.site20.com/125498
  512    |  www.site20.com/479312
  512    |  www.site20.com/6543
  512    |  www.site20.com/2713
  6143   |  www.site20.com/32346
  6143   |  www.site20.com/6478


I probably won't be storing the whole link, just the bit on the end and then rebuilding the link before I display it... unless storing the whole link makes my site load faster.

Then of course:

-----------------------------
|UserID  |  referral links  |
-----------------------------
  512    |  www.site1.com/join/4564
  512    |  www.site2.com/register/1298
  512    |  www.site3.com/register/09312
  512    |  www.site4.com/join/126543
  512    |  www.site5.com/referral/231
  6143   |  www.site1.com/join/32
  6143   |  www.site2.com/register/566478


And

-----------------------------
|UserID  |  Profile Links   |
-----------------------------
  512    |  www.site1.com/profile/4564
  512    |  www.site2.com/user/1298
  512    |  www.site3.com/user/09312
  512    |  www.site4.com/profile/126543
  512    |  www.site5.com/user/231
  6143   |  www.site1.com/profile/32
  6143   |  www.site2.com/user/566478


And a few more for wishlists, About Me, Profile Pic, etc.

My question is, is this the most efficient way to do something like this?  Or would it be better to use 1 row for all of user 512's site1.com links, and 1 row for 512's site2.com links, etc, since I'll usually be pulling all of them almost every time I pull any of them?  If so, how would I do that?
Please explain your answers well, and links to more info would be appreciated, since I'm a complete newbie to databases.

Thanks!

More info...

The average user (95%) will probably have 500-10,000 links in each table (if I don't decide to limit it) and there will be probably between 60-100 site tables. I don't see ever having over a million users, and I'll probably be lucky to have 20,000, but better to plan ahead just in case.

Not all users will have links for all sites.

Data usage-

Most of the time I'll be doing something along the lines of:

Display all site3.com links for user 512.

I'd also like to be able to display a random set of (for example) all site5.com links.

I assume this means indexing both columns of my table?
(Not an answer, but some pitfalls that make it difficult to design this for efficiency.)


More than half the users will have only one link.
Some users will each have over 100K links.
More than half the sites will have only one page.
Some sites will each have over 100K pages.


What does it mean?  It means that any form of indexing, compression, etc, needs to be flexible enough to handle such extremes.

(I experimented with a similar experiment, quitting after a million or so links.  Those are extrapolations of what I encountered.)