I am creating a web application which will enable customers to publish short status updates much like Twitter. The only method I'm able to think about storing these posts is to possess a large "status_updates" table that stores EVERY user's status updates:

| table: status_updates              |
| id | user_who_posted | update_text |

This process requires something similar to this SQL query to obtain each user's updates:

SELECT * FROM status_updates where user_who_posted="username"

and i believe that would not be very inefficient. It is possible to better method of carrying this out?

Develop a user table, and also have the user_id be an integer foreign answer to that user table. Then, build a catalog around the user_id area to permit rapid retrieval.

In a nutshell:

|  status_id  |  user_id  |  status  |
|          1  |        1  |  Woot!   |
|          2  |        1  |  Yeah!   |
|          3  |        2  |  Hello!  |

|  user_id  |  username  |
|        1  |  'Joe'     |
|        2  |  'John'    |

Then, to retrieve, you'd do that:

    status_updates s
    inner join users u on
        s.user_id = u.user_id
    u.username = 'John'

This can retrieve:

|  username  |  status  |
|  John      |  Hello!  |

Use that what you should. That'll be very performant on countless rows, as long as you construct your indexes right. What RDBMS are you currently using, in order to show you the best place for your?

This really can be quite efficient as lengthy while you correctly setup a catalog for that status_updates table on user.

If you're truly concerned about the table becoming very, large you might want to consider horizontal partitioning of the database(s).

It might be faster not to possess a string in your search criteria, and rather have your user changed having a surrogate key:

SELECT update_text
FROM status_updates
    ON status_updates.user_id = users.user_id
WHERE users.username = 'username'

Clearly, indexing and potentially partitioning your table might be helpful for scalability.