Originally Posted by Old Pedant
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 */
tweet VARCHAR( 150 ),
CONSTRAINT FOREIGN KEY tweet_users_fk (userid) REFERENCES users(userid),
INDEX tweet_userid_idx ( userid )
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:
FROM users AS U, tweets A T
WHERE U.userid = T.userid
AND U.username = 'John Doe'
ORDER BY whenTweeted DESC
to get the most recent 10 tweets by "John Doe".
And it *will* be a very fast query.
That's what I was thinking. Now assume I have 20,000 tweets in a database. Or twitter's seemingly infinite number. Is this when php is no longer an option? I mean, must take forever to search through every
tweet (server-side, not myself).