CODEWITHSUNDEEP
mysql
Marian Petrov
Marian Petrov

Reputation: 625

Updating row in tables with huge amount of data

I have to update the views of the current post. From table posts witch have data > 2 millions. And the loading time of the page is slow.

Tables:

idpost | iduser | views | title        |
1      | 5675   | 45645 | some title   |
2      | 345    | 457   | some title 2 |
6      | 45     | 98    | some title 3 |
and many more... up to 2 millions

And iduser have Index, idpost have Primary key.

If I seprate the data and make a new table post_views and use LEFT JOIN to get the value of the views. At first it will be fast since the new table is still small, but over time she as well will have > 2 millions rows. And again it will be slow. How you deal with huge table ?

Upvotes: 1

Views: 114

Answers (1)

mmdemirbas
mmdemirbas

Reputation: 9168

Split the table

You should split the table to separate different things and prevent repetition of title data. This will be a better design. I suggest following schema:

posts(idpost, title)    
post_views(idpost, iduser, views)

Updating views count

You will need to update views of only one row at a time. Because, someone views your page, then you update related row. So, just one row update at a time without a searching overhead (thanks to key & index). I didn't understand how this can make an overhead?

Getting total views

Probably, you run a query like this one:

SELECT SUM(views) FROM post_views WHERE idpost = 100

Yes, this can make an overhead. A solution may be to create anew table total_post_views and update corresponding value in this table after each update on post_views. Thus, you will get rid of the LEFT JOIN and access total view count directly.

But, updating for each update also makes an overhead. To increase performance, you can give up updating total_post_views after each update on post_views. If you choose this way, you can perform update:

  • periodically, say in each 30sec,
  • after certain update counts of post_views, say for each 30 update.

In this way, you will get approximate results, of course. If this is tolerable, then I suggest you to go this way.

Upvotes: 1

Related Questions