Database Reference
In-Depth Information
| 5 | 100 | 2 |
+----+-------+-------------+
mysql>
UPDATE expdata SET value = value * 10;
mysql>
SELECT * FROM expdata;
+----+-------+-------------+
| id | value | log10_value |
+----+-------+-------------+
| 1 | 0.1 | -1 |
| 2 | 1 | 0 |
| 3 | 10 | 1 |
| 4 | 100 | 2 |
| 5 | 1000 | 3 |
+----+-------+-------------+
With that implementation, using a
log10_value
column that stores the
LOG10()
values
of the
value
column, the
SELECT
query shown earlier can be rewritten:
SELECT
*
FROM
expdata
WHERE
log10_value
<
2
;
The optimizer can use the index on
log10_value
, something not true of the original
query that referred to
LOG10(value)
.
Using triggers this way to simulate a function-based index improves query performance
for
SELECT
queries, but you should also consider the disadvantages of the technique:
• It requires extra storage for the secondary column.
• It requires more processing for statements that modify the original column (to
activate the triggers that keep the secondary column and its index up to date).
The technique is therefore most useful if the workload for the table skews more toward
retrievals than updates. It is less beneficial for a workload that is mostly updates.
The preceding example uses a
log10_value
column that is useful for several types of
lookups, from single-row to range-based expressions. But functional indexes can be
useful even for situations in which
most
queries select only a single row. Suppose that
you want to store large data values such as PDF or XML documents in a table, but also
want to look them up quickly later (for example, to access other values stored in the
same row such as author or title). A
TEXT
or
BLOB
data type might be suitable for storing
the values, but is not very suitable for finding them. (Comparisons in a lookup operation
are slow for large values.) To work around this problem, use the following strategy:
1. Compute a hash value for each document and store it in the table along with the
document. For example, use the
MD5()
function, which returns a 32-byte string of
hexadecimal characters. That's still long for a comparison value, but much shorter
than a full-column comparison based on contents of very long documents.
2. To look up the row containing a particular document, compute the document hash
value and search the table for that value. For best performance, index the hash
