Database Reference
In-Depth Information
such as
latin1_swedish_ci
. If your filesystem is set up to use pathnames in a
different character set, change the character set and collation.
• The
path
column has a maximum length of 255 characters, which limits you to
page paths no longer than that.
• The
path
column is indexed as a
PRIMARY
KEY
to require unique values. Either a
PRIMARY
KEY
or
UNIQUE
index is required because we will implement the hit-
counting algorithm using an
INSERT
statement with an
ON
DUPLICATE
KEY
UPDATE
clause to insert a row if none exists for the page or update the row if it does exist.
(
Recipe 13.12
explains
ON
DUPLICATE
KEY
UPDATE
.)
• The table is set up to count page hits for a single document tree, such as when your
web server is used to serve pages for a single domain. If you institute a hit count
mechanism on a host that serves multiple virtual domains, you may want to add a
column for the domain name. This value is available in the
SERVER_NAME
value that
Apache puts into your script's environment. In this case, the
hitcount
table index
should include both the hostname and the page path.
The general logic involved in hit counter maintenance is to increment the
hits
column
of the row for a page, and then retrieve the updated counter value:
UPDATE
hitcount
SET
hits
=
hits
+
1
WHERE
path
=
'
page
path
';
SELECT hits FROM hitcount WHERE path = '
page
path
'
;
Unfortunately, with that approach, you might not get the correct value. If several clients
request the page simultaneously, several
UPDATE
statements execute in close temporal
proximity and the
SELECT
statements that follow won't necessarily get the corresponding
hits
value. This can be avoided by using a transaction or by locking the
hitcount
table,
but that slows down hit counting. MySQL provides a solution that enables each client
to retrieve its own count, no matter how many simultaneous updates occur:
UPDATE
hitcount
SET
hits
=
LAST_INSERT_ID
(
hits
+
1
)
WHERE
path
=
'
page
path
'
;
SELECT
LAST_INSERT_ID
();
The basis for updating the count here is
LAST_INSERT_ID(
expr
)
, discussed in
Recipe 13.12
. The
UPDATE
statement finds the relevant row and increments its counter
value. The use of
LAST_INSERT_ID(hits+1)
rather than just
hits+1
tells MySQL to treat
the value as though it were an
AUTO_INCREMENT
value. This enables it to be retrieved in
the second statement using
LAST_INSERT_ID()
. The
LAST_INSERT_ID()
function re‐
turns a connection-specific value, so it always corresponds to the preceding
UPDATE
for
the same connection. In addition, the
SELECT
statement doesn't query a table, so it's very
fast.
However, there's still a problem. What if the page isn't listed in the
hitcount
table? In
that case, the
UPDATE
statement finds no row to modify and you get a counter value of
zero. You could deal with this problem by requiring that any page that includes a hit
counter must be registered in the
hitcount
table before the page goes online. This is
