I do not know too many things about databases, so a lot of questions concerning architecture came up lately. Two of these things are:If I have a table with a lot of entries(millions probably), how can I make the select queries faster? I thought about sorting the table alphabetically and then splitting it in two, but that doesn't seem to make things easier for me. Do you have any suggestions?I have a table user and one message. In message I should have sender_id and receiver_id. From what I know, I can't make them both foreign keys for user, so I have to pick one of them. Doesn't this, however, lead to data inconsistency(which, as far as I know, is bad)? What is the right approach here?I do not think that it matters, but I use MySQL 5.5.
Database architecture decisions
database design
Add indexes on the columns used in the WHERE clauses. In an extreme case, use sharding, but that's if you're talking about billions of rows (at least), not millions. Additionally, for complex queries with multiple joins and subqueries, the structure of the query can make a big difference. The query optimizer of the DB should take care of that, but sometimes it messes up or needs more information (e.g. updated DB statistics). You really need an experienced DBA at that point. And of course the structure of the DB also has a big effect. Sometimes you have to denormalize, or just fix a fundamentally broken schema.You most definitely can have foreign key constraints on multiple columns of a table that all refer to the same column in a different table. So do that.