프로젝트하면서 mysql 관해 작업하는데 OTL 로 만든 글귀하나;;
시간내서 이 책도 봐야겠다;; 볼께 너무 많아 ㅜ.ㅜ
Pros and Cons of a Denormalized Schema
A denormalized schema works well because everything is in the same table, which
avoids joins.
If you don’t need to join tables, the worst case for most queries—even the ones that
don’t use indexes—is a full table scan. This can be much faster than a join when the
data doesn’t fit in memory, because it avoids random I/O.
A single table can also allow more efficient indexing strategies. Suppose you have a
web site where users post their messages, and some users are premium users. Now
say you want to view the last 10 messages from each of the premium users. If you’ve
normalized the schema and indexed the publishing dates of the messages, the query
might look like this:
Normalization and Denormalization | 141
mysql> SELECT message_text, user_name
-> FROM message
-> INNER JOIN user ON message.user_id=user.id
-> WHERE user.account_type='premium'
-> ORDER BY message.published DESC LIMIT 10;
To execute this query efficiently, MySQL will need to scan the published index on
the message table. For each row it finds, it will need to probe into the user table and
check whether the user is a premium user. This is inefficient if only a small fraction
of users have premium accounts.
The other possible query plan is to start with the user table, select all premium users,
get all messages for them, and do a filesort. This will probably be even worse.
The problem is the join, which is keeping you from sorting and filtering simultaneously
with a single index. If you denormalize the data by combining the tables and
add an index on (account_type, published), you can write the query without a join.
This will be very efficient:
mysql> SELECT message_text,user_name
-> FROM user_messages
-> WHERE account_type='premium'
-> ORDER BY published DESC
-> LIMIT 10;
발췌: High Performance_mysql_2nd_edition 중에서
