Hi guys

I have a table where rows are inserted each time someone visits any
page on a site.
Something like:

table site_visits:
creation_date datetime, session_id varchar(100)

//session_id is fetched from PHP. If already present in the table then
I will not insert new row, but only update the creation_date

As you can imagine the table grows up all the time and I need to
remove the old records. Im was going to create a cronjob that runs
once a day and that will remove all entries older than 1 day.

Do you think it would be better with a trigger? Something like: Each
time an insert occurs remove all the records older than 1 day? Or is
it an unnecessary and costly double query each time someone visits the
site?

"pac Man" <querystrange (AT) gmail (DOT) com> wrote

Personally I'd run it every day (or hourly, depending) but not on every
insert. Doing it on every insert will slow your insert down because you
would then be doing an insert plus a delete/select. That said, if your site
isn't busy and you can afford the extra overhead, then doing it via a
trigger might be a very simply and easy to implement solution - just not
very efficient.
--
Brian Cryer
http://www.cryer.co.uk/brian

On 28 Feb, 16:03, "Brian Cryer" <not.h... (AT) localhost (DOT) invalid> wrote:

You right. It would have been a quite stupid solution. I looked into
MySQL Event, probably the best way to solve this task.

On Mon, 28 Feb 2011 02:51:49 -0800 (PST), pac Man
<querystrange (AT) gmail (DOT) com> wrote:

PHP has a function to implement this kind of session
garbage collection (gc). Implement the callback for gc,
set the parameters in php.ini and you're done.
PHP will call the callback function every now and then.

http://www.php.net/manual/en/functio...ve-handler.php
http://www.php.net/manual/en/session.configuration.php

Not necessary.
Best regards,
--
( Kees Nuyt
)
c[_]

On 2/28/2011 12:29 PM, Kees Nuyt wrote:
PHP's garbage collection will do NOTHING for data in the database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Mon, 28 Feb 2011 13:40:02 -0500, Jerry Stuckle
<jstucklex (AT) attglobal (DOT) net> wrote:

PHP session handlers can do anything you want them to do,
including executing SQL statements.

Best regards,
--
( Kees Nuyt
)
c[_]

On 3/2/2011 1:08 PM, Kees Nuyt wrote:
Which is completely separate from PHP's garbage collection.

As for using a session handler - he'd have to have some way to ensure
the handler runs on the schedule he wants - which is basically
impossible. And of course he'd have to write his own session handler
from scratch.

A very stupid way to handle what can easily be done with a cron job and
a two or three line MySQL script.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Wed, 02 Mar 2011 15:20:37 -0500, Jerry Stuckle
<jstucklex (AT) attglobal (DOT) net> wrote:

You brought non-session related garbage collection in,
not me.

Not necessary, he only has to expand the session_gc()
function.
In many cases the exact schedule is not very important,
as long as the clean-up runs every now and then. In my
opinion this might be such a case. It's up to the OP to
choose.

Not all environments allow cron jobs.
This would be an alternative.

Anyway, I think the OP has enough material to decide
upon. Please feel free to have your usual last word on
this, but I'm done.
Best regards,
--
( Kees Nuyt
)
c[_]