So far I have been logging any events to do with a user - and their actions in a database which the administrators can then see, in the format of their user ID, an action ID (which PHP converts to a fixed string when fetched) and a timestamp of when said action occured. This, however, causes a lot of SQL queries (one extra on each action that is logged - which apart form clicks is anything that results in a query (generally)). Whilst this may seem like overkill, the application requires it. Whilst I was developing it, the lag wasn’t really noticable, but when being rolled out to users, and the log table has ballooned in size I’ve decided to look for a better option.
So, on to my question:- I thought of doing this in a XML format, with each log, then an action which can be saved as a string with say: User x deleted user y; which is currently not possible, and storing all the other data in a format PHP does not need to convert to strings each time (i.e human readable). It occured then that opening, writing and closing a file is likely to take as long (or longer) than MySQL, well, it just seemed a little time consuming.
Is there a preferred method for doing this type of thing that I happened to have missed, or was I heading along the right tracks? Each user would have their own file, due to the collisions that may otherwise occur, but are locked files going to be an issue, doing it like this?
Hmm, yes, I did think of something like that - although for my purposes the XML files would all be saved for a couple of weeks and output from that - the logs must be displayed within the application, which I then thought all the file operations may get a bit messy - it would require a shorter amount of code to do it in SQL. I was just wondering whether there was an industry standard way of doing this, to keep overheads low that I haven’t thought of?
Have you considered using a cron job once per 24hrs to create an xml file for the previous day’s activity then emptying the table? You could then download the generated xml file to your local computer and go through it offline.