plugins - How to do database for download stats?

admin管理员组

文章数量:1327665

So I have a existing wordpress site and we are building now download section for it. Downloads will be custom post type and files. Now we need to have some stats, like number of downloads per cpt (needed) and who downloaded (logged in users) (nice to have). There would few hundred (2009 users each download few files (3-5) each day.

We have been thinking of just creating new table to database and each time someone download something, we add there new line with ref. to cpt id, user id and date. But then again thinking, this might be too heavy for db? Now how would you do this? What would be more efficient and nicer way of doing it?

So I have a existing wordpress site and we are building now download section for it. Downloads will be custom post type and files. Now we need to have some stats, like number of downloads per cpt (needed) and who downloaded (logged in users) (nice to have). There would few hundred (2009 users each download few files (3-5) each day.

We have been thinking of just creating new table to database and each time someone download something, we add there new line with ref. to cpt id, user id and date. But then again thinking, this might be too heavy for db? Now how would you do this? What would be more efficient and nicer way of doing it?

• plugins
• database
• mysql
• statistics

Share Improve this question edited Jul 28, 2020 at 13:33 mozboz 2,62811 gold badge1212 silver badges2323 bronze badges asked Jul 28, 2020 at 12:05 user995317user995317 16522 silver badges99 bronze badges 1

• 1 Keep in mind that this question has little to do with WP and your proposed table and wether it would be too heavy and completely unrelated to WP other than circumstance. You could eliminate all mention of WP and ask on StackOverflow. Also, questions here need to be specific, and factually answerable, this isn't a discussion forum, so you need to be able to mark an answer as not just the reply you liked best, or most useful, but the answer, the canonically correct answer for all people who have this question – Tom J Nowell ♦ Commented Jul 28, 2020 at 13:35

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

This is more of a MySQL question, but I think your design is fine and is unlikely to create any database problems, even for a lot of rows (up to 1,000,000). The reason for this is:

Why this is fine

• Nearly everything you do with this table should be writes, and there will only be a 'small' number of writes per day (up to a thousand?). This is not a good idea if you're doing many writes per second but your quantity is very low.
• Unless you need to run reports more than say, once per hour you will almost never do any serious 'reads' on this table which would create any load at all for MySQL
• Just having the data there in a separate table has no effect on the rest of your database or Wordpress - it can't slow anything else down unless this table is huge and you hit disk space limits.
• This design is not suitable and needs optimisations if, for example you start doing something like displaying a report from this table on every single Wordpress page load, but I understand from your question that this is for your own statistics and reporting usage.

So go for it. A couple of suggestions for when you're designing this and to allow for future expandion:

Design Considerations

• The table might get big, so design it efficiently (e.g. use the smallest datatypes possible that fit your requirements).
• Be careful with indexes if you start to write to the table more! Having more indexes slows down writes and are mainly useful when you are reading from the table (i.e. running your reports).
• Having this table in Wordpress database for when it's written to is great. It's easy and fast. But if you do big reporting queries against this table it will be slow or might create a lot of load when your table is big. To mitigate this, think about a process where you copy the data from this table to a different database which is where the reports get run. This means that those big heavy reporting queries won't affect your running Wordpress, but you still have the advantage that the writes are fast. Copying the whole table should be easy and fast.

本文标签： pluginsHow to do database for download stats