PHP - Best way to log user activity and display them on different pages?

Question

I have got my website loggin user activity into a .txt file. I want to be able to show these results on my Admin area in separate pages. So page one shows 1-50 results and so on.

But the problem I have is it's set out like this in the .txt file

User: Admin IP Address: xx.xxx.xxx.xx Host Address: xxxxxxxxxxxxxxxx Date And Time: Monday 20th of September 2010 11:44:18 AM URL: http://colemansystems.psm2.co.uk/ Browser: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_3; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.62 Safari/534.3 Refering URL:

With a gap of seven lines, between each set of information. I was thinking about putting it into a MySQL table but wouldn't that get very big over time?

Any help is appreciated!

Answer 1

So what if it gets big? That's what databases are for.

Let me assure you that the [one-time] cost of setting up a database and a table will be much less than maintaining a homebrew (as in custom-format) data file in the long run.

What if you want to know what happened between 2010-09-25 and -26 via the IP address 1.2.3.4? Are you going to write a function? (It's a single statement in SQL.) Are you going to scan the whole file? (Proper DBMS's will just use the indices. MySQL will use at least one index.)

I'm half inclined to say "try them both and see how the DB approach wins in the long run" because the advantages are too numerous to list.

Answer 2

A database table seems more suited for this if you will be querying it, rather than just looking at the logs in some monitor (eg unix tail).

It will get very big over time, which is why you can prune it by deleting older entries every now and then if you notice performance drops. You can setup a script that runs a DELETE query on the table on all entries older than, say, 1 month. Then you can schedule that script to run daily.

Another option will be to use partitioning on date if you truly need all the logs even older ones, but that's a bit more advanced.

Frankly, assuming you setup proper indexes, the row count should enter the tens of millions before you notice any degradation of performance.

Answer 3

Any modern database, including MySQL, will handle queries on a properly indexed table in reasonable time with several tens of millions of rows in it. That's what they're for.

My usual rule of thumb is that for any table up to 10,000 rows and straightforward queries you don't even need to think too hard about data access paths or anything else. In the 10,000 to 1 million row range I would be giving it significant attention to ensure the table was properly indexed, and beyond a million row then more advance management techniques may be required to keep performance acceptable (although for a simple log table this would be less of an issue).