[Old Pedant]

Originally Posted by KULP

So I have userid for primary key in user table, and then I have a table for 'tweets.' Don't I now have to search through every tweet to match up userid's?

Yes and no. *YOU* don't have to "search" at all. MySQL does.

And the trick to making that search efficient is to create an index on the USERID field in the TWEETS table.

Something like this:

CREATE TABLE tweets (
    tweetid INT AUTO_INCREMENT PRIMARY KEY, /* optional */
    userid INT,
    tweet VARCHAR( 150 ),
    whenTweeted DATETIME,
    CONSTRAINT FOREIGN KEY tweet_users_fk (userid) REFERENCES users(userid),
    INDEX tweet_userid_idx ( userid )
) ENGINE=INNODB;

The CONSTRAINT says that userid is intimately linked to the primary key userid in the users table; the INDEX makes sure that MySQL can get essentially instant access to all tweets, by userid.

So a typical query might then be:

SELECT T.tweet
FROM users AS U, tweets A T
WHERE U.userid = T.userid
AND U.username = 'John Doe'
ORDER BY whenTweeted DESC
LIMIT 10

to get the most recent 10 tweets by "John Doe".

And it *will* be a very fast query.