Josee Posted June 13, 2011 Share Posted June 13, 2011 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 More sharing options...
Larry Posted June 13, 2011 Share Posted June 13, 2011 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 More sharing options...
Josee Posted June 13, 2011 Author Share Posted June 13, 2011 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 More sharing options...
Larry Posted June 14, 2011 Share Posted June 14, 2011 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 More sharing options...
Matt Posted June 14, 2011 Share Posted June 14, 2011 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 More sharing options...
Larry Posted June 15, 2011 Share Posted June 15, 2011 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 More sharing options...
Recommended Posts