Is this the optimal MySQL database schema for a website that can become huge?

Im sketching out a database layout for a website that has the potential to become huge with 100's of queries a minute.

I was thinking about doing the following:

user table

id

name

(few more fields)

Pages (this one will become the biggest table)

id

tit开发者_如何学Cel

img

text

restaurant (this will be the row that connects the pages to the user table, i was planning on creating an index on this one to increase speed)

So im wondering if creating an index for the 'restaurant' row will increase the speed of my queries or if there is any other way to speed up things?

Thanks in advance!

If you need to do some query like :

select *
from pages
where restaurant = ...

Or like :

select *
from user
    inner join pages on pages.restaurant = user.id
where user.name = '...'

Or any other condition on the restaurant column, then, you'll probably want to add an index on that column, to avoid scanning all lines on the pages table.

But note that useful/necessary indexes will almost always depend on the kind of queries you'll be doing.

Which means that it's not quite possible to accurately guess which indexes you'll need -- first, you need to know how you will access you data.

Note : you should read the How MySQL Uses Indexes section of MySQL's manual : it contains stuff that's interesting to know ;-)

As a test, you can always run your query in your preferred tool and add EXPLAIN in front. This will show you what indices are being used and/or which temporary tables had to be created etc.

EXPLAIN select *
from pages
where restaurant = ...

If you're using the InnoDB storage, you should not just use 'an index' but make use of FOREIGN KEY. Thus, you will also decrease potential integrity problems.

Suggestion: do not use restaurant as a name. Add some more tables and it will be difficult to keep track what references what. Why not call it user_id? (This is a matter of personal preference, though.)