Indexing for High Performance - High Performance MySQL - page 157

mysql> SELECT word, crc FROM words WHERE crc = CRC32('gnu');
+---------+------------+
| word | crc |
+---------+------------+
| codding | 1774765869 |
| gnu | 1774765869 |
+---------+------------+
The correct query is as follows:
mysql> SELECT word, crc FROM words WHERE crc = CRC32('gnu')AND word = 'gnu';
+------+------------+
| word | crc |
+------+------------+
| gnu | 1774765869 |
+------+------------+
To avoid problems with collisions, you must specify both conditions in the WHERE
clause. If collisions aren't a problem—for example, because you're doing statistical
queries and you don't need exact results—you can simplify, and gain some efficiency,
by using only the CRC32() value in the WHERE clause. You can also use the FNV64() func-
tion, which ships with Percona Server and can be installed as a plugin in any version
of MySQL. It's 64 bits long, very fast, and much less prone to collisions than CRC32() .
Spatial (R-Tree) indexes
MyISAM supports spatial indexes, which you can use with partial types such as GEOME
TRY . Unlike B-Tree indexes, spatial indexes don't require your WHERE clauses to operate
on a leftmost prefix of the index. They index the data by all dimensions at the same
time. As a result, lookups can use any combination of dimensions efficiently. However,
you must use the MySQL GIS functions, such as MBRCONTAINS() , for this to work, and
MySQL's GIS support isn't great, so most people don't use it. The go-to solution for
GIS in an open source RDBMS is PostGIS in PostgreSQL.
Full-text indexes
FULLTEXT is a special type of index that finds keywords in the text instead of comparing
values directly to the values in the index. Full-text searching is completely different
from other types of matching. It has many subtleties, such as stopwords, stemming and
plurals, and Boolean searching. It is much more analogous to what a search engine does
than to simple WHERE parameter matching.
Having a full-text index on a column does not eliminate the value of a B-Tree index on
the same column. Full-text indexes are for MATCH AGAINST operations, not ordinary
WHERE clause operations.
We discuss full-text indexing in more detail in Chapter 7 .