Creating an Index
|
|
There are four kinds of indexes in MySQL: a primary key, a unique index, a full-text index, and an ordinary index.
Creating a Primary Key
A primary key is an index on a field where each value is unique and none of the values are NULL.
| Note | The term primary key is, strictly speaking, a logical term, but MySQL uses it to denote a physical index. When MySQL indicates that a primary key exists, there is always an associated index. Throughout this text, the term key indicates the presence of a physical index. |
To create a primary key when creating a table, use PRIMARY KEY at the end of the field definitions, with a list of the fields to be included:
CREATE TABLE tablename(fieldname columntype NOT NULL, [fieldname2...,] PRIMARY KEY(fieldname1 [,fieldname2...]));
Note that the keyword NOT NULL is mandatory when creating a primary key; primary keys cannot contain a null value. MySQL warns you if you forget to specify this:
mysql> CREATE TABLE pk_test(f1 INT, PRIMARY KEY(f1)); ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead
To create a primary key on an already existing table, you can use the ALTER keyword:
ALTER TABLE tablename ADD PRIMARY KEY(fieldname1 [,fieldname2...]);
Choosing a primary key for the customer table is fairly easy. The id field lends itself to this because each customer has a different id and there are no null fields. Either of the name fields would not be ideal, as there may be duplicates at some stage. To add a primary key to the id field of the customer table, you need to change the field to not allow nulls and then add the primary key. You can do this in one statement, as follows:
mysql> ALTER TABLE customer MODIFY id INT NOT NULL, ADD PRIMARY KEY(id); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0
You can see the changes you've made to the table with this statement by examining the columns:
mysql> DESCRIBE customer; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(11) | | PRI | 0 | | | first_name | varchar(30) | YES | | NULL | | | surname | varchar(40) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
The id field does not have a YES in the Null column, indicating that it no longer can accept null values. It also has PRI in the Key column, indicating the primary key.
Primary keys can also consist of more than one field. Sometimes there is no one field that can uniquely identify a record. To add a primary key in this instance, separate the fields with a comma:
mysql> CREATE TABLE pk2(id INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id,id2)); Query OK, 0 rows affected (0.00 sec)
or as follows if the table already exists:
mysql> ALTER TABLE pk2 ADD PRIMARY KEY(id,id2); Query OK, 0 rows affected (0.01 sec)
The sales table from earlier chapters does not yet have a key:
mysql> SHOW COLUMNS FROM sales; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | code | int(11) | YES | | NULL | | | sales_rep | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | value | int(11) | YES | | NULL | | +-----------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Let's assume you add a new record with the same code as an existing record:
mysql> SELECT * FROM sales; +------+-----------+------+-------+ | code | sales_rep | id | value | +------+-----------+------+-------+ | 1 | 1 | 1 | 2000 | | 2 | 4 | 3 | 250 | | 3 | 2 | 3 | 500 | | 4 | 1 | 4 | 450 | | 5 | 3 | 1 | 3800 | | 6 | 1 | 2 | 500 | | 7 | 2 | NULL | 670 | +------+-----------+------+-------+ 7 rows in set (0.00 sec) mysql> INSERT INTO sales VALUES(7,3,3,1000); Query OK, 1 row affected (0.00 sec)
There is no problem so far. Even though there are now two records with a code of 7, there is nothing in the table structure that disallows this. But now, with your new knowledge of the reasons for using a primary key, you decide to make the code field a primary key:
mysql> ALTER TABLE sales MODIFY code INT NOT NULL,ADD PRIMARY KEY(code); ERROR 1062: Duplicate entry '7' for key 1
You have a duplicate value for the code field, and by definition a primary key should always be unique. Here, you would have to either remove or update the duplicates or use an ordinary index that allows duplicates. Most tables work better with a primary key, though. In this situation, it's easy to update the responsible record:
mysql> UPDATE sales SET code=8 WHERE code=7 AND sales_rep=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE sales MODIFY code INT NOT NULL,ADD PRIMARY KEY(code); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0
| Tip | I worked on one system where a "unique" field turned out to have thousands of duplicates because of a combination of having no primary key and nonexistent locking. It's always better to add keys, especially a primary key, when you create a table. |
Creating an Ordinary Index
An index that is not primary allows duplicate values (unless the fields are specified as unique). As always, it's best to create the index at the same time as you create the table:
CREATE TABLE tablename(fieldname columntype, fieldname2 columntype, INDEX [indexname] (fieldname1 [,fieldname2...]));
You can also create more than one index when the table is created, just separating them with commas:
CREATE TABLE tablename(fieldname columntype, fieldname2 columntype, INDEX [indexname1] (fieldname1,fieldname2),INDEX [indexname2] (fieldname1 [,fieldname2...]));
You can always create an index at a later stage, though, with this code:
ALTER TABLE tablename ADD INDEX [indexname] (fieldname1 [,fieldname2...]);
or with the following code:
mysql> CREATE INDEX indexname on tablename(fieldname1 [,fieldname2...]);
Both of these statements ask for an index name, although with the CREATE INDEX statement the index name is mandatory. If, in the ALTER TABLE…ADD INDEX… statement, you do not name the index, MySQL will assign its own name based on the fieldname. MySQL takes the first field as the index name if there is more than one field in the index. If there is a second index starting with the same field, MySQL appends _2, then _3, and so on to the index name.
The following sales table has a primary key, but it could also do with an index on the value field. You may quite often be searching for records with a value greater or less than a certain amount, or ordering by the value:
mysql> SHOW COLUMNS FROM sales; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | code | int(11) | | PRI | 0 | | | sales_rep | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | value | int(11) | YES | | NULL | | +-----------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE sales ADD INDEX(value); Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0
| Note | You can use the keyword KEY instead of INDEX in MySQL statements if you want. I prefer INDEX because KEY usually refers to the logical structure, and INDEX usually refers to the actual physical index on the disk. |
Creating a Full-Text Index
You can create full-text indexes in MyISAM tables, on any CHAR, VARCHAR, or TEXT fields. A full-text index is designed to allow easy searching for keywords in text fields in large tables.
To create a full-text index when the table is created, use this syntax:
CREATE TABLE tablename (fieldname columntype, fieldname2 columntype, FULLTEXT(fieldname [,fieldname2...]));
The optional keyword INDEX can be added, as in this syntax:
CREATE TABLE tablename (fieldname columntype, fieldname2 columntype, FULLTEXT INDEX(fieldname [,fieldname2...]));
To create a full-text index once the table is already in existence, use this syntax:
ALTER TABLE tablename ADD FULLTEXT [indexname] (fieldname [,fieldname2...]);
or the following code:
CREATE FULLTEXT INDEX indexname ON tablename(fieldname [,fieldname2...]);
Let's create a table and try to create full-text indexes on some of the fields, as follows:
mysql> CREATE TABLE ft(f1 VARCHAR(255),f2 TEXT,f3 BLOB,f4 INT); Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE ft ADD FULLTEXT (f1,f2); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
The fields f1 and f2 are VARCHAR and TEXT, respectively, so a full-text index is allowed:
mysql> ALTER TABLE ft ADD FULLTEXT (f1,f4); ERROR 1005: Can't create table './firstdb/#sql-52eb_4f.frm' (errno: 140) mysql> ALTER TABLE ft ADD FULLTEXT (f2,f3); ERROR 1005: Can't create table './firstdb/#sql-52eb_4f.frm' (errno: 140)
In these examples, the field f4 is of type INT and f3 is of type BLOB, so a full-text index is not allowed in either case.
Using a Full-text Index
Let's create a table with a full-text index and insert some book titles into it to test it:
mysql> CREATE TABLE ft2(f1 VARCHAR(255),FULLTEXT(f1)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ft2 VALUES('Waiting for the Barbarians'), ('In the Heart of the Country'), ('The Master of Petersburg'), ('Writing and Being'), ('Heart of the Beast'), ('Heart of the Beest'), ('The Beginning and the End'), ('Master Master'), ('A Barbarian at my Door'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 To return the results of a full-text search, you use the MATCH() function, and MATCH() a field AGAINST() a value, as in this example, which looks for occurrences of the word Master:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('Master'); +--------------------------+ | f1 | +--------------------------+ | Master Master | | The Master of Petersburg | +--------------------------+ 2 rows in set (0.01 sec) It's no coincidence that Master Master appeared first, even though it was added second. MySQL calculates a relevance result for each match and returns the results in this order.
| Note | Remember that searches on TEXT fields are not case sensitive, nor are they on VARCHAR or CHAR fields declared without the BINARY keyword. |
Noise Words
Now you run another search:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('The Master'); +--------------------------+ | f1 | +--------------------------+ | Master Master | | The Master of Petersburg | +--------------------------+ 2 rows in set (0.00 sec) These results are not as you may expect. Most of the titles contain the word the, and The Beginning and the End contains it twice, yet is not reflected. There are a number of reasons for this:
-
MySQL has what is called a 50-percent threshold. Any words that appear in more than 50 percent of the fields are treated as noise words, meaning that they are ignored.
-
Any words of three or fewer letters are excluded from the index.
-
There is a predefined list of noise words, with the included.
So, The Beginning and the End didn't have a chance!
| Warning | If you have a table with only one record, all words will be noise words, so a full-text search would not return anything! Tables with very few records can also increase the likelihood of words being treated as noise words. |
The following query returns nothing, even though the word for does appear in the data because for is a word of three or fewer characters, and by default these are excluded from the index:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('for'); Empty set (0.00 sec)
Relevance
You are not limited to using the MATCH() function in the WHERE condition; you can return the results, too, as follows:
mysql> SELECT f1, (MATCH(f1) AGAINST ('Master')) FROM ft2; +-----------------------------+--------------------------------+ | f1 | (MATCH(f1) AGAINST ('Master')) | +-----------------------------+--------------------------------+ | Waiting for the Barbarians | 0 | | In the Heart of the Country | 0 | | The Master of Petersburg | 1.2245972156525 | | Writing and Being | 0 | | Heart of the Beast | 0 | | Heart of the Beest | 0 | | A Barbarian at my Door | 0 | | Master Master | 1.238520026207 | | The Beginning and the End | 0 | +-----------------------------+--------------------------------+ 9 rows in set (0.00 sec) Your relevance scores may not match the examples, as MySQL occasionally makes changes to the weighting scheme.
The relevance calculation is quite intelligent. It is based on the number of words in the index field of the row, the number of unique words in that row, the total number of words in the result, the number of records that contain the particular word, and the weight of the word. Rare words are weighted more heavily, and the more records that contain the word, the less its weight.
MySQL can return the relevance as well as the required fields at no extra cost in time because the two calls to the MATCH() function are identical:
mysql> SELECT f1,(MATCH(f1) AGAINST ('Master')) FROM ft2 WHERE MATCH(f1) AGAINST ('Master'); +--------------------------+--------------------------------+ | f1 | (MATCH(f1) AGAINST ('Master')) | +--------------------------+--------------------------------+ | Master Master | 1.238520026207 | | The Master of Petersburg | 1.2245972156525 | +--------------------------+--------------------------------+
Boolean Full-Text Searches
One of MySQL 4's most useful enhancements is the ability to perform a Boolean full-text search. This makes use of a full set of features to search for words, combinations of words, portions of words, and so on (see Table 4.1).
| Operator | Description |
|---|---|
| + | The word following is mandatory and must be present in every row returned. |
| - | The word following is prohibited and must not be present in any row returned. |
| < | The word following has a lower relevance than other words. |
| > | The word following has a higher relevance than other words. |
| ( ) | Used to group words in subexpressions. |
| ~ | The word following makes a negative contribution to the row relevance (this is not the same as the - operator, which excludes the row altogether if the word is found, or as the < operator, which still assigns a positive, though lower, relevance to the word). |
| * | The wildcard, indicating zero or more characters. It can only appear on the end of a word. |
| " | Anything enclosed in double quotes is taken as a whole. |
Boolean full-text searches do not take the 50-percent threshold limit into account. To perform a Boolean full-text search, you use the IN BOOLEAN MODE clause:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('+Master -Petersburg' IN BOOLEAN MODE); +---------------+ | f1 | +---------------+ | Master Master | +---------------+ 1 row in set (0.00 sec) In this example, the word Petersburg is excluded, so The Master of Petersburg is not returned even though Master appears in the title.
Note the difference between these two sets of results:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('Country Master' IN BOOLEAN MODE); +-----------------------------+ | f1 | +-----------------------------+ | In the Heart of the Country | | The Master of Petersburg | | Master Master | +-----------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('+Country Master' IN BOOLEAN MODE); +-----------------------------+ | f1 | +-----------------------------+ | In the Heart of the Country | +-----------------------------+ 1 row in set (0.00 sec) The word Country is mandatory in the second search (by default a word is optional), so The Master of Petersburg and Master Master are not returned.
The next example demonstrates a common cause of confusion:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('+Dog Master' IN BOOLEAN MODE); +--------------------------+ | f1 | +--------------------------+ | The Master of Petersburg | | Master Master | +--------------------------+ 2 rows in set (0.00 sec) This result may seem surprising if you compare it to the previous example, but because the word Dog is three or fewer letters, it is excluded for purposes of the search.
The next two examples demonstrate the difference between searching for a whole word and a part of a word (making use of the * operator):
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('Barbarian' IN BOOLEAN MODE); +------------------------+ | f1 | +------------------------+ | A Barbarian at my Door | +------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('Barbarian*' IN BOOLEAN MODE); +----------------------------+ | f1 | +----------------------------+ | A Barbarian at my Door | | Waiting for the Barbarians | +----------------------------+ 2 rows in set (0.01 sec) By default only whole words are matched, unless the * operator is used.
The next three examples demonstrate the use of the > and < operators to increase and decrease the weightings respectively:
mysql> SELECT f1,MATCH(f1) AGAINST ('Heart Beest Beast' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('Heart Beest Beast' IN BOOLEAN MODE); +-----------------------------+------+ | f1 | m | +-----------------------------+------+ | In the Heart of the Country | 1 | | Heart of the Beast | 2 | | Heart of the Beest | 2 | +-----------------------------+------+ 3 rows in set (0.00 sec) mysql> SELECT f1,MATCH(f1) AGAINST ('Heart Beest >Beast' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('Heart Beest >Beast' IN BOOLEAN MODE); +-----------------------------+------+ | f1 | m | +-----------------------------+------+ | In the Heart of the Country | 1 | | Heart of the Beast | 2.5 | | Heart of the Beest | 2 | +-----------------------------+------+ 3 rows in set (0.00 sec) The > operator increases the relevance of Heart of the Beast.
mysql> SELECT f1,MATCH(f1) AGAINST ('Heart <Beest Beast' IN BOOLEAN MODE) As m FROM ft2 WHERE MATCH(f1) AGAINST ('Heart <Beest Beast' IN BOOLEAN MODE); +-----------------------------+-----------------+ | f1 | m | +-----------------------------+-----------------+ | In the Heart of the Country | 1 | | Heart of the Beast | 2 | | Heart of the Beest | 1.6666667461395 | +-----------------------------+-----------------+ 3 rows in set (0.00 sec) The < operator decreases the relevance of Heart of the Beest.
The next five examples demonstrate the difference between the < operator, which adds a decreased, yet positive, weight to the match; the ~ operator, which places a negative weight on the match; and the - operator, which prohibits the match. The first example is a basic Boolean search, with a weighting of 1 for a match:
mysql> SELECT *,MATCH(f1) AGAINST ('Door' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('Door' IN BOOLEAN MODE); +------------------------+------+ | f1 | m | +------------------------+------+ | A Barbarian at my Door | 1 | +------------------------+------+ 1 row in set (0.00 sec) Next, the < operator reduces this weighting to roughly 2/3, which is still a positive weight:
mysql> SELECT *,MATCH(f1) AGAINST ('<Door' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('<Door' IN BOOLEAN MODE); +------------------------+------------------+ | f1 | m | +------------------------+------------------+ | A Barbarian at my Door | 0.66666668653488 | +------------------------+------------------+ 1 row in set (0.00 sec) The ~ operator reduces this weight to a negative, and so, since the result is less than 0 when matched with A Barbarian at my Door, the row is not returned:
mysql> SELECT *,MATCH(f1) AGAINST ('~Door' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('~Door' IN BOOLEAN MODE); Empty set (0.00 sec) Using the ~ operator in conjunction with an ordinary match allows us to see how much the weighting is reduced by, which in this case is 0.5:
mysql> SELECT *,MATCH(f1) AGAINST ('~Door Barbarian*' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('~Door Barbarian*' IN BOOLEAN MODE); +-----------------------------+------+ | f1 | m | +-----------------------------+------+ | A Barbarian at my Door | 0.5 | | Waiting for the Barbarians | 1 | +-----------------------------+------+ 2 rows in set (0.01 sec) Finally, this next example shows the difference between the ~ and - operators, where the - operator prohibits the match when Door is found:
mysql> SELECT *,MATCH(f1) AGAINST ('-Door Barbarian*' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('-Door Barbarian*' IN BOOLEAN MODE); +-----------------------------+------+ | f1 | m | +-----------------------------+------+ | Waiting for the Barbarians | 1 | +-----------------------------+------+ 1 row in set (0.00 sec) The next example demonstrates grouping words into a sub-expression:
mysql> SELECT f1,MATCH(f1) AGAINST ('+Heart +(<Beest >Beast)' IN BOOLEAN MODE) As m FROM ft2 WHERE MATCH(f1) AGAINST ('+Heart +(<Beest >Beast)' IN BOOLEAN MODE); +--------------------+------------------+ | f1 | m | +--------------------+------------------+ | Heart of the Beast | 1.25 | | Heart of the Beest | 0.83333337306976 | +--------------------+------------------+ 2 rows in set (0.00 sec) The + operator applies to the entire substring in parentheses, which means that at least one of Beest and Beast must be present in the string. In the Heart of the Country does not appear then, because neither Beest nor Beast is present. Compare this to the following code:
The next example demonstrates a commonly used form of the search, where each of the supplied words is mandatory:
mysql> SELECT f1,MATCH(f1) AGAINST ('+Heart +<Beest +>Beast)' IN BOOLEAN MODE) AS m FROM ft2 WHERE MATCH(f1) AGAINST ('+Heart +<Beest +> Beast)' IN BOOLEAN MODE); Empty set (0.00 sec) Nothing is returned because no rows contain all of Heart, Beest, and Beast at the same time.
The next two examples demonstrate the difference between a search using the "" operators and one without them. The "" operators allow you to search for an exact match on a phrase:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('the Heart of the' IN BOOLEAN MODE); +-----------------------------+ | f1 | +-----------------------------+ | In the Heart of the Country | | Heart of the Beast | | Heart of the Beest | +-----------------------------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ('"the Heart of the"' IN BOOLEAN MODE); +-----------------------------+ | f1 | +-----------------------------+ | In the Heart of the Country | +-----------------------------+ 1 row in set (0.00 sec) Be careful not to leave out the initial single quotes when making use of the double quote operator. If you do, you'll effectively have no operators. For example:
mysql> SELECT * FROM ft2 WHERE MATCH(f1) AGAINST ("the Heart of the" IN BOOLEAN MODE); +-----------------------------+ | f1 | +-----------------------------+ | In the Heart of the Country | | Heart of the Beast | | Heart of the Beest | +-----------------------------+ 3 rows in set (0.00 sec) | Warning | Full-text indexes can take a long time to generate and cause OPTIMIZE statements to take a long time as well. |
Creating a Unique Index
A unique index is the same as an ordinary index, except that no duplicates are allowed.
To create a unique index when the table is created, use the following syntax:
CREATE TABLE tablename (fieldname columntype, fieldname2 columntype, UNIQUE(fieldname [,fieldname2...]));
Or, if the table already exists, you can use either this syntax:
ALTER TABLE tablename ADD UNIQUE [indexname ] (fieldname [,fieldname2...]);
or this syntax:
CREATE UNIQUE INDEX indexname ON tablename(fieldname [,fieldname2...]);
If the index contains a single field, that field cannot contain duplicate values:
mysql> CREATE TABLE ui_test(f1 INT,f2 INT,UNIQUE(f1)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ui_test VALUES(1,2); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ui_test VALUES(1,3); ERROR 1062: Duplicate entry '1' for key 1
Although the field f1 was not specified as UNIQUE when it was created, the existence of the unique index prevents any duplication. If the index contains more than one field, individual field values can be duplicated, but the combination of field values making up the entire index cannot be duplicated:
mysql> CREATE TABLE ui_test2(f1 INT,f2 INT,UNIQUE(f1,f2)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ui_test2 VALUES(1,2); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO ui_test2 VALUES(1,3); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO ui_test2 VALUES(1,3); ERROR 1062: Duplicate entry '1-3' for key 1
Creating Indexes from Part of a Field
For VARCHAR, CHAR, BLOB, and TEXT columns, MySQL allows you to create an index that does not use the entire field. For example, although the customer surname may be up to 40 characters, it's likely that the surname will differ in the first 10 characters. By only using the first 10 characters for the index, the index will be a lot smaller. This makes updates and inserts quicker (you're writing one quarter what you would have if you'd used the full column after all), and it won't affect SELECT speed as long as you don't cut the index too short. Making a one-character index for the surname would defeat the purpose of an index.
To create an index from part of a field, just put the size in parentheses after the column name. For example, to create a 10-character index on the surname field in the customer table, use the following code:
mysql> ALTER TABLE customer ADD INDEX (surname(10)); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0
| Note | You cannot create an index (except for a full-text index) on an entire BLOB or TEXT field, so in this case you'd have to specify the index size. |
|
|
