Mysql – INT or TIMESTAMP for the change time column in a logging table

logsMySQLtimestamp

We're having an argument over what to use for storing change date in a new log table for our site.

One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime query in PHP to a UNIX integer and query based on that (WHERE log_date BETWEEN [convert 12/25/2012 to timestamp] AND [convert 12/31/2012 to timestamp]).

The other side says, use TIMESTAMP for this; we can then use the native tools to query. (WHERE log_date BETWEEN "2012-12-25" AND "2012-12-31").

Since this is a logging table, we're not concerned about the 1970-2038 range of TIMESTAMP.

Which makes more sense?

Best Answer

I have done it both ways. I prefer using TIMESTAMP because it makes adhoc queries easier. If you only use a GUI to query the logs, then use whatever that programmer wants.

This one boils down to a style question because there isn't much difference in the functionality or performance.

Related Solutions

MySQL – How to Log DB Changing Queries and Users

This sounds like a perfect use of TRIGGERS to me. You say you have transactions, so you must already be using InnoDB. Attach triggers on INSERT, UPDATE, and DELETE on the tables you care about, each one triggering an INSERT into a log table AFTER the table event. There's your SELECT filter, done. You also have total control over what you log and how.

This is a higher level solution, closer to the application layer, not a low-level system log solution where you have an exact copy of the complete SQL query. But I think it does address some of your concerns.

InnoDB Triggers have access to stored procedures and all the MySQL functions, so I presume you can find the user's login name in there somewhere. The log time is just NOW().

You may end up with a lot of triggers to manage, depending on your number of tables, but it's just a copy/paste job for each one. I maintain the code for all triggers in a single reference SQL file along with the table definitions and views for that database.

I've never tested triggers in a transaction-rich environment to make sure the trigger only fired after the transaction was committed. The docs are thin on this subject, but they do say, "An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully." Sounds conclusive to me. Now you also know you're not logging things that didn't actually work.
OR set up replication, even to another mysqld instance on the same box if necessary. Turn on your mysqld full query logging on the slave instance. Failed transactions don't get replicated, and neither do SELECTs.

MySQL query optimizer ignoring smaller scan on TIMESTAMP column, cardinality

For these queries and if your WHERE is as you have shown and you also have ORDER BY rf_timestamp you can use this index, which should be far better than a single index on si_id or a single index on rf_timestamp:

ALTER TABLE rf
  ADD INDEX si_id__rf_timestamp__IX         -- choose a name for the index
    (si_id, rf_timestamp) ;

With a table of this size, adding this index will take some time and the table will be locked in the mean time, so it would be better if you did this when there is not much traffic and work by others in the database.

Best Answer

Related Solutions

MySQL – How to Log DB Changing Queries and Users

MySQL query optimizer ignoring smaller scan on TIMESTAMP column, cardinality

Related Question