Database Reference
In-Depth Information
values to apply the function to them, and the function values are not indexed. The result
is diminished performance.
Some database systems permit an index to be defined on a function of a column, such
that you can index
LOG10(value)
. MySQL does not support this capability, but there is
a workaround:
1. Define a secondary column to store the function values and index that column.
2. Define triggers that keep the secondary column up to date when the original column
is initialized or modified.
3. Refer directly to the secondary column in queries so that the optimizer can use the
index on it for efficient lookups.
The following example illustrates this technique, using a table designed to store values
that lie along an exponential curve:
CREATE
TABLE
expdata
(
id
INT
UNSIGNED
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY
,
value
FLOAT
,
#
original
values
log10_value
FLOAT
,
#
LOG10
()
function
of
original
values
INDEX
(
value
),
#
index
on
original
values
INDEX
(
log10_value
)
#
index
on
function
-
based
values
);
The table includes
value
and
log10_value
columns to store the original data values
and those values transformed with
LOG10()
. The table also indexes both columns.
Create an
INSERT
trigger to initialize the
log10_value
value from
value
for new rows,
and an
UPDATE
trigger to keep
log10_value
up to date when
value
changes:
CREATE
TRIGGER
bi_expdata
BEFORE
INSERT
ON
expdata
FOR
EACH
ROW
SET
NEW
.
log10_value
=
LOG10
(
NEW
.
value
);
CREATE
TRIGGER
bu_expdata
BEFORE
UPDATE
ON
expdata
FOR
EACH
ROW
SET
NEW
.
log10_value
=
LOG10
(
NEW
.
value
);
To test the implementation, insert and modify some data and check the result of each
operation:
mysql>
INSERT INTO expdata (value) VALUES (.01),(.1),(1),(10),(100);
mysql>
SELECT * FROM expdata;
+----+-------+-------------+
| id | value | log10_value |
+----+-------+-------------+
| 1 | 0.01 | -2 |
| 2 | 0.1 | -1 |
| 3 | 1 | 0 |
| 4 | 10 | 1 |
