Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I do not know too many things about databases, so a lot of questions concerning architecture came up lately. Two of these things are:

  1. 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?

  2. 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.

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted
  1. 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.

  2. 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.

share|improve this answer
    
There is a singular/plural issue with the phrasing "multiple columns be foreign key", because it sounds like there is one foreign key bound to multiple columns. But in this case you want several foreign keys, each bound to a single column. –  Pete May 1 '12 at 13:02
    
@Pete: reworded to reflect the fact that technically it's a foreign key constraint you create. –  Michael Borgwardt May 1 '12 at 13:16
    
maybe you want to say "multiple foreign key constraints"; each constraint is only for a single column. –  kevin cline May 1 '12 at 18:30
add comment

If I have a table with a lot of entries(millions probably), how can I make the select queries faster?

You need to add indexes to the table - how this is done is different from database to database, so you need to consult you MySQL 5.5 documentation. As for how to choose a good index - books were written on this subject... Here is one introduction to the topic.

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

You know wrong. You certainly can make each of them a foreign key to user, independently.

share|improve this answer
2  
"You know wrong" - epic! –  Mithir May 1 '12 at 13:14
add comment

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?

That's a HUGE open-ended question. First, remember this:

  • You should never optimize unless you are actually seeing performance problems. Are you seeing performance problems?

If you are, then you should further investigate to determine what those performance problems are. What's your query look like? Does it utilize any indexes? If you use EXPLAIN it'll tell you what sort of indexes are being used, and help explain what's slow/poorly optimized about the query.

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.

Why can't you? MySQL WILL let you do this- the RIGHT approach is to make them both foreign keys.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.