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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0