MySQL Cookbook, 2nd Edition

Web Page Access Logging

Problem

You want to know more about a page than just the number of times it’s been accessed, such as the time of access and the host from which the request originated.

Solution

Maintain a hit log rather than a simple counter.

Discussion

The hitcount table used in Web Page Access Counting records only the access count for each page registered in it. If you want to record other information about page access, use a different approach. Suppose that you want to track the client host and time of access for each request. In this case, you need to log a row for each page access rather than just a count. But you can still maintain the counts by using a multiple-column index that combines the page path and an AUTO_INCREMENT sequence column:

CREATE TABLE hitlog
(
  path  VARCHAR(255)
          CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  hits  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  t     TIMESTAMP,
  host  VARCHAR(255),
  INDEX (path,hits)
) ENGINE = MyISAM;

See Web Page Access Counting for notes on choosing the character set and collation for the path column.

To insert new rows into the hitlog table, use this statement:

INSERT INTO hitlog (path, host) VALUES(path_val,host_val);

For example, in a JSP page, hits can be logged like this:

<c:set var="host"><%= request.getRemoteHost () %></c:set> <c:if test="${empty host}"> <c:set var="host"><%= request.getRemoteAddr () %></c:set> </c:if> <c:if test="${empty host}"> <c:set var="host">UNKNOWN</c:set> </c:if> <sql:update dataSource="${conn}"> ...