Return to Answer

replaced http://stackoverflow.com/ with https://stackoverflow.com/

Source Link

edited May 23, 2017 at 12:40

Observations

Your $maxlifetime parameter is unused.
One of the conditions is redundant: any session older than two weeks should be discarded unconditionally.
The capitalization of your column names is unconventional. Usually, identifiers are lowercase, and SQL keywords are ALL CAPS. (Note: identifiers in MySQL are case-sensitive on Unix, but not Windows case-sensitive on Unix, but not Windows.)

Proposal

DELETE FROM session
    WHERE last_activity < CURRENT_TIMESTAMP() - INTERVAL 2 WEEK
    OR (rememberuser = 'N' AND last_activity < CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE);

Advantages

This is a fixed query with no parameters, which keeps things simple.
It's almost English-like in readability.

Caveats

This requires your last_activity column to be of the DATETIME or TIMESTAMP type. You could also make it work with integer Unix timestamps instead, using

  DELETE FROM session
      WHERE last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 2 WEEK)
      OR (rememberuser = 'N' AND last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE));

This assumes that the last_activity times were set according to the database server's clock, not the application server's clock. Otherwise, if they are running on separate machines, or if you are careless with your treatment of timezones, you could end up with clock skew. Therefore, it's best to pick one clock and stick with it consistently. I prefer to use the database server's clock, for several reasons:
1. If your application runs on multiple application servers, all connecting to a central database, then the database clock is the natural official time source.
2. Your queries can be simpler, as illustrated above.
3. Storing the timestamps in a DATETIME column is more meaningful than storing them as integers.

So, to make this work, the last_activity times have to be inserted or updated using CURRENT_TIMESTAMP() or NOW(), or established by default when a TIMEZONE column has no specified value.

Admittedly, this formulation uses MySQL-specific date / time functions, so it is less portable. Similar solutions exist for other database systems, though.

Observations

Your $maxlifetime parameter is unused.
One of the conditions is redundant: any session older than two weeks should be discarded unconditionally.
The capitalization of your column names is unconventional. Usually, identifiers are lowercase, and SQL keywords are ALL CAPS. (Note: identifiers in MySQL are case-sensitive on Unix, but not Windows.)

Proposal

DELETE FROM session
    WHERE last_activity < CURRENT_TIMESTAMP() - INTERVAL 2 WEEK
    OR (rememberuser = 'N' AND last_activity < CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE);

Advantages

This is a fixed query with no parameters, which keeps things simple.
It's almost English-like in readability.

Caveats

This requires your last_activity column to be of the DATETIME or TIMESTAMP type. You could also make it work with integer Unix timestamps instead, using

  DELETE FROM session
      WHERE last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 2 WEEK)
      OR (rememberuser = 'N' AND last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE));

This assumes that the last_activity times were set according to the database server's clock, not the application server's clock. Otherwise, if they are running on separate machines, or if you are careless with your treatment of timezones, you could end up with clock skew. Therefore, it's best to pick one clock and stick with it consistently. I prefer to use the database server's clock, for several reasons:
1. If your application runs on multiple application servers, all connecting to a central database, then the database clock is the natural official time source.
2. Your queries can be simpler, as illustrated above.
3. Storing the timestamps in a DATETIME column is more meaningful than storing them as integers.

So, to make this work, the last_activity times have to be inserted or updated using CURRENT_TIMESTAMP() or NOW(), or established by default when a TIMEZONE column has no specified value.

Admittedly, this formulation uses MySQL-specific date / time functions, so it is less portable. Similar solutions exist for other database systems, though.

Observations

Your $maxlifetime parameter is unused.
One of the conditions is redundant: any session older than two weeks should be discarded unconditionally.
The capitalization of your column names is unconventional. Usually, identifiers are lowercase, and SQL keywords are ALL CAPS. (Note: identifiers in MySQL are case-sensitive on Unix, but not Windows.)

Proposal

DELETE FROM session
    WHERE last_activity < CURRENT_TIMESTAMP() - INTERVAL 2 WEEK
    OR (rememberuser = 'N' AND last_activity < CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE);

Advantages

This is a fixed query with no parameters, which keeps things simple.
It's almost English-like in readability.

Caveats

This requires your last_activity column to be of the DATETIME or TIMESTAMP type. You could also make it work with integer Unix timestamps instead, using

  DELETE FROM session
      WHERE last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 2 WEEK)
      OR (rememberuser = 'N' AND last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE));

This assumes that the last_activity times were set according to the database server's clock, not the application server's clock. Otherwise, if they are running on separate machines, or if you are careless with your treatment of timezones, you could end up with clock skew. Therefore, it's best to pick one clock and stick with it consistently. I prefer to use the database server's clock, for several reasons:
1. If your application runs on multiple application servers, all connecting to a central database, then the database clock is the natural official time source.
2. Your queries can be simpler, as illustrated above.
3. Storing the timestamps in a DATETIME column is more meaningful than storing them as integers.

So, to make this work, the last_activity times have to be inserted or updated using CURRENT_TIMESTAMP() or NOW(), or established by default when a TIMEZONE column has no specified value.

Admittedly, this formulation uses MySQL-specific date / time functions, so it is less portable. Similar solutions exist for other database systems, though.

Source Link

answered Feb 20, 2014 at 4:10

200_success

145.7k
22
192
481

Observations

Your $maxlifetime parameter is unused.
One of the conditions is redundant: any session older than two weeks should be discarded unconditionally.
The capitalization of your column names is unconventional. Usually, identifiers are lowercase, and SQL keywords are ALL CAPS. (Note: identifiers in MySQL are case-sensitive on Unix, but not Windows.)

Proposal

DELETE FROM session
    WHERE last_activity < CURRENT_TIMESTAMP() - INTERVAL 2 WEEK
    OR (rememberuser = 'N' AND last_activity < CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE);

Advantages

This is a fixed query with no parameters, which keeps things simple.
It's almost English-like in readability.

Caveats

This requires your last_activity column to be of the DATETIME or TIMESTAMP type. You could also make it work with integer Unix timestamps instead, using

  DELETE FROM session
      WHERE last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 2 WEEK)
      OR (rememberuser = 'N' AND last_activity < UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 30 MINUTE));

This assumes that the last_activity times were set according to the database server's clock, not the application server's clock. Otherwise, if they are running on separate machines, or if you are careless with your treatment of timezones, you could end up with clock skew. Therefore, it's best to pick one clock and stick with it consistently. I prefer to use the database server's clock, for several reasons:
1. If your application runs on multiple application servers, all connecting to a central database, then the database clock is the natural official time source.
2. Your queries can be simpler, as illustrated above.
3. Storing the timestamps in a DATETIME column is more meaningful than storing them as integers.

So, to make this work, the last_activity times have to be inserted or updated using CURRENT_TIMESTAMP() or NOW(), or established by default when a TIMEZONE column has no specified value.

Admittedly, this formulation uses MySQL-specific date / time functions, so it is less portable. Similar solutions exist for other database systems, though.