MySQL: data duplication and joining
Assuming I have two tables, a source and an article, and I want to read an article with specific details of its source, I can either (1) use a join for the two tables; or (2) duplicate details in the article record (which will make the data block larger, but the query will be very simple) which would be more efficient?
a source to share
Depends on the data. Let's say you have a huge table of articles and a small table of authors. If you want to do a lot of queries that will get some article data and author name (this is in the default article table), then you have a simple primary key lookup for each "author" row, and a small table would probably fit in memory. so there won't be a huge performance boost including the author name in the article table. In addition, this denormalization will also make the article table larger (each author name will be duplicated many times) You will use more of your cache.
On the other hand, if you wanted to query the number of articles for each author, getting this data from two tables would mean concatenating many rows each time. But if you included that number in the "authors" table, getting it would only mean one search and increment for each article added. Therefore, if you are interested in results like this, denormalization might make sense.
a source to share
which would be more efficient?
Simply put (perhaps too simple): you are trading memory for CPU cycles - which can lead to poor caching and poor performance.
The only way to get your question right is to accept your environment and measure your performance. Don't forget to include "correctly" indexed tables. Create realistic database load - for example, make sure you don't hit the cache for the same rows over and over.
Ask yourself in advance if what performance (1%, 10%, 100%) is worth starting denormalization.
a source to share
This is a design decision, which means that without all the details of your analysis (goals, constraints, user requirements, etc.), but a few rules that I use;
1 / A joins between two tables, usually not very expensive and this is a simple example to set up (for example, you say the update will be small and I assume it doesn't have a lot of insert / delete, and basically picks, so this there will likely be a situation where indexing will speed up)
2 / When designing a schema, first normalize it to the highest possible / sane, and then later, when real world scenarios prove that it is worth denormalizing. (And generally, the decision to normalize and then denormalize specific elements works well enough, failing to normalize generally doesn't work well.
3 / Over a period of time, normalization pays for itself (in later years, when you try to make some changes to the system, a well-designed framework is really welcome and appreciated)
4 / Denormalising seems to me best for reporting situations in which ad hoc queries will be used. Or in other words, the main reason I see denormalization makes life easier for report writers who have a high request-write / usage rate
a source to share
If read performance is a priority, you can use Materialized Views . Since MySQL doesn't support them (I think) you can simulate them .
This solution keeps the original database normalized but you get the performance given by simple queries from MVs.
a source to share
data duplication can bring you great performance. Note I wrote because you will have caching issues. On the other hand, when duplicating data, you make it difficult to work with your system (BTW, you are breaking the normal form of the DB). If the price you have to pay is just one table join, then just pay. Make sure you have indexex on the columns you are joining, and then the price won't be that expensive at all.
Bottom line: never duplicate data unless critical.
a source to share