CODEWITHSUNDEEP
mysqlviewquery-performance
Thomas
Thomas

Reputation: 10679

Performance of Views versus Tables for temporal changing data

I have a table for news articles, containing amongst others the author, the time posted and the word count for each article. The table is rather large, containing more than one million entries and growing with an amount of 10.000 entries each day.

Based on this data, a statistical analysis is done, to determine the total number of words a specific author has published in a specific time-window (i.e. one for each hour of each day, one for each day, one for each month) combined with an average for a time-span. Here are two examples:

Current practice is to start a script at the end of each defined time-window via cron-job, which calculates the count and the averages and stores it in a separate table for each time-window (i.e. one for each hourly window, one for each daily, one for each monthly etc...).

The calculation of sums and averages can easily be done in SQL, so I think Views might be a more elegant solution to this, but I don't know about the implications on performance.

Are Views an appropriate solution to the problem described above?

Upvotes: 0

Views: 68

Answers (2)

Artem Arzamasov
Artem Arzamasov

Reputation: 26

I think you can use materialize views for it. It's not really implemented in MySQL, but you can implement it with tables. Look at

Upvotes: 1

Randy
Randy

Reputation: 16673

views will not be equivalent to your denormalization.

if you are moving aggregate numbers somewhere else, then that has a certain cost, which you are paying - in order to keep the data correct, and a certain benefit, which is much less data to look through when querying.

a view will save you from having to think too hard about the query each time you run it, but it will still need to look through the larger amount of data in the original tables.

while i'm not a fan of denormalization, since you already did it, i think the view will not help.

Upvotes: 1

Related Questions