Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hello Larry,

 

On page 180 you write:

MySQL has four types of indexes: INDEX (the standard), UNIQUE (which requires each row to have a unique value for that column), FULLTEXT (for performing FULLTEXT searches, discussed later in this chapter), and PRIMARY KEY (which is just a particular UNIQUE index and one you’ve already been using). Note that a column should only ever have a single index on it, so choose the index type that’s most appropriate.

 

Am I right in thinking this is not always the case, and that you can for instance both have a FULLTEXT index and a UNIQUE index on the same column? I looked at the 5.1 MySQL manual ("create-index" page and "How MySQL Uses Indexes" page) and didn't find anything on this subject, so I probably didn't look in the right place! But I tried creating a UNIQUE index on a VARCHAR(1000) column that already had a FULLTEXT index, and I got no error message.

 

mysql> SHOW INDEX FROM citations
+-----------+--------+-----------+---------+----------+-----------+-----------+------+--------+------+----------+
| Table     | Non_   | Key_name  | Seq_in_ | Column_  | Collation | Cardinal- | Sub_ | Packed | Null | Index_   |
|           | unique |           | index   | name     |           | ity       | part |        |      | type     |
+-----------+--------+-----------+---------+----------+-----------+-----------+------+--------+------+----------+
| citations |      0 | PRIMARY   |       1 | id_      | A         |        16 | NULL | NULL   |      | BTREE    |
|           |        |           |         | citation |           |           |      |        |      |          |
| citations |      0 | citation_ |       1 | citation | A         |        16 |   80 | NULL   |      | BTREE    |
|           |        | unique    |         |          |           |           |      |        |      |          |
| citations |      1 | citation_ |       1 | citation | NULL      |      NULL | NULL | NULL   |      | FULLTEXT |
|           |        | fulltext  |         |          |           |           |      |        |      |          |
+-----------+--------+-----------+---------+----------+-----------+-----------+------+--------+------+----------+
3 rows in set (0,00 sec)

 

Can I keep it this way, or shall I have problems with this table if I keep both indices?

 

With thanks for your help,

Link to comment
Share on other sites

This is a bit of a gray area. I believe MySQL will let you put multiple indexes on the same column, but phpMyAdmin, for example, will report that as a problem. The only situation where I ever put multiple indexes on the same column would be in a login example, where the email address or username might be UNIQUE and there's also an index on the combination of the email address or username and password.

 

FULLTEXT and UNIQUE are two totally different kinds of indexes, so I don't think it's a problem to use both on the same column except that the idea of putting a UNIQUE index on a column that merits a FULLTEXT index is like nails on a chalkboard for me. Putting a UNIQUE index on a column with long text values will be terribly inefficient, so I would re-think your needs there.

Link to comment
Share on other sites

FULLTEXT and UNIQUE are two totally different kinds of indexes, so I don't think it's a problem to use both on the same column except that the idea of putting a UNIQUE index on a column that merits a FULLTEXT index is like nails on a chalkboard for me. Putting a UNIQUE index on a column with long text values will be terribly inefficient, so I would re-think your needs there.

 

Thank you very much for your answer, Larry. This column is for literary quotations, and I limited the UNIQUE index to the first 80 characters (out of a maximum of 1000 characters). Is that still very inefficient? Would it be better to use PHP to check that I haven't already entered this quotation in the database?

 

With thanks for your help,

Link to comment
Share on other sites

Good, limiting to 80 characters is a smart choice and should minimize the impact on performance. In fact, a UNIQUE index on the first 80 characters is probably better than using PHP to check for uniqueness, because PHP would check the entire string, meaning a subtle difference anywhere in the string would not be caught.

Link to comment
Share on other sites

Larry,

 

I hope this doesn't sound too moronic on my part (since databases are more my strength), but I notice that you use "UNIQUE" to define indexes in "PHP 6 and MySQL 5: Visual QuickPro Guide" and you use "UNIQUE KEY" in the "E-Commerce" book. What is the main difference between the two? Are UNIQUE KEY indexes only applied to secondary keys, or are they just a way of defining another key in a table (which could just as easily be done with UNIQUE or KEY)?

 

Information online about the differences between these is really sparse, so if you could elaborate on this I would be extremely thankful!

 

Thanks,

 

Matt

Link to comment
Share on other sites

Sorry for the confusion. The keywords INDEX and KEY are the same in MySQL. And both are optional when using UNIQUE, so UNIQUE, UNIQUE KEY, and UNIQUE INDEX are all the same.

 

I personally tend to write INDEX and UNIQUE, but sometimes I use the output from phpMyAdmin as the SQL in the book, and it prefers KEY.

Link to comment
Share on other sites

 Share

×
×
  • Create New...