Yes and no. *YOU* don't have to "search" at all. MySQL does.
Originally Posted by KULP
And the trick to making that search efficient is to create an index on the USERID field in the TWEETS table.
Something like this:
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.
CREATE TABLE tweets (
tweetid INT AUTO_INCREMENT PRIMARY KEY, /* optional */
tweet VARCHAR( 150 ),
CONSTRAINT FOREIGN KEY tweet_users_fk (userid) REFERENCES users(userid),
INDEX tweet_userid_idx ( userid )
So a typical query might then be:
to get the most recent 10 tweets by "John Doe".
FROM users AS U, tweets A T
WHERE U.userid = T.userid
AND U.username = 'John Doe'
ORDER BY whenTweeted DESC
And it *will* be a very fast query.