Do self-joins entail higher performance penalty than multi-table joins?
When I asked about the right approach to a data modeling problem--using one or two tables-- some users responded that self-join开发者_如何学Gos present a performance liability. Is this true given the following scenario (MySQL database):
- The model is for a a Q&A website where questions and answers are stored in the same table, denoted by a TypeId column.
- Where a question can have many answers; it can be favourited, and voted on by many users.
- Qs and As have their own comments
The alternative is to model questions, answers, and comments each in its respective table, and thus avoid self joins. I'm yet to decide on which approach to use. While the question relates to join performance, any additional advice on both use cases would be appreciated.
Thanks
You should put questions and answers in separate tables not particularly from a performance perspective but because they are logically different things and to do otherwise is confusing.
From a performance perspective there's no particular reason why a single self join would be slow - I suspect that the problem you are thinking of is if you need to query recursively which is slow in MySQL.
Also regarding performance your design means your key consists of two fields rather than one as you need to specify in a condition whether you want a question or an answer. Wider keys take longer to compare - not a serious issue, but it might make a difference in some situations.
精彩评论