Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 320

column. Because the hash value is a function of the document, the index on it is, in
effect, a functional index.
The result is that lookups based on the hash-value column will perform much more
efficiently than lookups based on the original document values.
9.6. Simulating TIMESTAMP Properties for Other Date and
Time Types
Problem
The TIMESTAMP data type provides auto-initialization and auto-update properties. You
would like to use these properties for other temporal data types that permit only constant
values for initialization and don't auto-update.
Solution
Use an INSERT trigger to provide the appropriate current date or time value at row-
creation time. Use an UPDATE trigger to update the column to the current date or time
when the row is changed.
Discussion
Recipe 6.7 describes the special TIMESTAMP and DATETIME initialization and update
properties enable you to record row-creation and row-modification times automati‐
cally. These properties are not available for other temporal types, although there are
reasons you might like them to be. For example, if you use separate DATE and TIME
columns to store row-modification times, you can index the DATE column to enable
efficient date-based lookups. (With TIMESTAMP or DATETIME , you cannot index just the
date part of the column.)
One way to simulate TIMESTAMP properties for other temporal data types is to use the
following strategy:
• When you create a row, initialize a DATE column to the current date and a TIME
column to the current time.
• When you update a row, set the DATE and TIME columns to the new date and time.
However, this strategy requires all applications that use the table to implement the same
strategy, and it fails if even one application neglects to do so. To place the burden of
remembering to set the columns properly on the MySQL server and not on application
writers, use triggers for the table. This is, in fact, a particular application of the general