Re: Database logic - Small or Large Tables for speed

[Phillip Ward]

If you only ever want to read the messages from(?) a single user, then "table-per-user" would quicker because you're only reading 1000 records, not a billion (do you really have a million users?).
You still need sensible Indexing on the table, though.

More likely, you're going to want to see messages for a number of users (i.e. more than one) at the same time; now "table-per-user" turns round and stabs you in the back! You have to start building your queries dynamically, to read the right tables, and if you have a conversation thread that involves more than about 60 people, your application will crash and burn because MySQL can only join 61(?) tables in a single query.

As Barry quite rightly says, "table-per-user" is almost always a terrible design choice.

Regards, Phill W.