MySQL in a Nutshell [63]
CREATE UNIQUE INDEX client_name
ON clients (client_lastname, client_firstname(4), rec_date);
In this example, an index is created called client_name. It is based on the last names of clients, the first four letters of their first names, and the dates that the records were created. This index is based on it being unlikely that a record would be created on the same day for two people with the same last name and a first name starting with the same four letters.
To see the indexes that have been created for a table, use the SHOW INDEXES statement. To remove an index, use the DROP INDEX statement.
Creating FULLTEXT indexes
CREATE FULLTEXT INDEX index
ON table (column, ...)
After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed or a totally new name.
You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default, but RTREE is also available as of version 5.0 of MySQL. The TYPE keyword is an alias for USING.
For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.
Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses:
CREATE FULLTEXT INDEX client_notes
ON clients (business_description, comments);
In this example, an index is created called client_notes. It is based on two columns, both of which are TEXT columns.
To see the indexes that have been created for a table, use the SHOW INDEXES statement. To remove an index, use the DROP INDEX statement.
Creating SPATIAL indexes
CREATE SPATIAL INDEX index
ON table (column, ...)
SPATIAL indexes can index spatial columns only in MyISAM tables. This is available starting with version 4.1 of MySQL. Here is an example in which first a table and then a spatial index is created:
CREATE TABLE squares
(square_id INT, square_name VARCHAR(100),
square_points POLYGON NOT NULL);
CREATE SPATIAL INDEX square_index
ON squares (square_points);
Notice that when we create the table, we specify that the column square_points is NOT NULL. This is required to be able to index the column. Let’s insert two rows of data:
INSERT INTO squares
VALUES(1000, 'Red Square',
(GeomFromText('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)))')) ),
(1000, 'Green Square',
(GeomFromText('MULTIPOLYGON(((3 3, 3 5, 5 5, 4 3, 3 3)))')) );
Here we added two squares by giving the five points of the polygon: the starting point (e.g., for the first row, x=0, y=0), the left top point (x=0, y=3), the right top point (x=3, y=3), the right bottom point (x=3, y=0), and the ending point (x=0, y=0) for good measure, which is the same as the starting point. So, the first row contains a square that is 3 × 3 in size, and the second contains a square that is 2 × 2 in size. Using the AREA( ) function we can find the area of each:
SELECT square_name AS 'Square',
AREA(square_points) AS 'Area of Square'
FROM squares;
+--------------+----------------+
| Square | Area of Square |
+--------------+----------------+
| Red Square | 9 |
| Green Square | 3 |
+--------------+----------------+
If we want to find which square contains a given point on a Cartesian plane (e.g., x=1, y=2), we can use the MBRContains( ) function like so:
SELECT square_name
FROM squares
WHERE
MBRContains(square_points, GeomFromText('POINT(1 2)'));
+-------------+
| square_name |
+-------------+
| Red Square |
+-------------+
To see how the index we added is involved, we would run an EXPLAIN statement using the same SELECT statement:
EXPLAIN SELECT square_name
FROM squares
WHERE