Which method is better?

Which structure is better?

I got a CMS of my own, where I want to track page hits.
I got 2 options now.

1. I create a "MEDIUM TEXT" column in the same table where page data is stored,
and I keep adding IP/UserAgent to this column on each page load.
Medium text column has a limit of 16MB, and my PHP memory_limit is over 30MB, so running out of memory is not an issue.
Most shared hosts have around that much, so even if site is moved, still it will not be a problem.

Problem is,
for every page load in front end, I will have to pull out that 16MB data, add my new string to it, and update the table record.
Can become an issue if traffic is high, server resources will get too stretched.
CONCAT does not seems to be working on MediumText and LongText on my shared server, not sure why...

2. I add a new table, and store IP/UserAgent in this
Front end page load will be faster, only a quick insert will be needed.
However, in the back end, when I will check stats,
then a very huge table will have to be read again and again.
Suppose there are 100 pages, then page hits data of all 100 pages will be stored in this table itself.
This can grow really huge.

So running one query would mean reading that huge table again and again.
Like, if I want to count total number of hits in one week, then on month, then on individual days may be...
Cannot set a limit in this type of count query...
Sure, there will be indexes etc, but am still worried about the size.

However, if its just in a medium text column, in the pages table itself,
then I just pull out 16MB data max, for specific page, and use PHP to do the rest of the processing.
This is cheaper on backend and probably faster too.

Which one would you suggest?

 

It might be worth it to look at something like Google Analytics. You don't have to write anything to your database, and if you put the .js file on your server, it wont make your page response time go up by much. It's free. I know some people don't like putting google stuff into their sites, but that's what I would do honestly.

Does your shared server have AWStats on it? You could write something that parses AWStats to give you an idea of page hits.

I wouldn't do option 1 because those queries would be rather expensive at scale depending on how much traffic you get.

If you don't want to add in Google Analytics, perhaps option 2 would do, but your database would start to bloat pretty quickly and would have a similar performance penalty when your table row size gets too big.

Does your hosting run on cPanel? If so, AWStats is buried in there somewhere.

 

AW stats is there, but this is needed for page specific logging, not site wide logging.
Log stats for a particular landing page, to be specific.
This is for the free distributable PowerPress CMS in my signature
https://13pp.co.uk/
Some users might use analytics, others might want something inbuilt.

I went with the second solution, separate table, and provided an option to clean the database in case a performance issue comes up.
Working good, not as resource intensive as I thought it would be...

 

I see. I haven't seen a use case like that yet, so the intent might have slipped my mind.
Glad the solution is working accordingly for you!

 

Thanks for taking the time to help, deeply appreciated