MyISAM unique keys being cut off at 64 bytes, causing collisions

Question

I've got a MySQL table that stores urls as unique keys. I'm starting to get collisions on my keys because it seems the keys themselves are only the first 64 bytes (or characters if you prefer, its a latin-1 collated) of any url. So if a url is over 64 characters and I've already got a similar url it throws an error.

For example:

SELECT l.link_id FROM mydb.links l WHERE 
url = 'http://etonline.com/tv/108475_Charlie_Sheen_The_People_Have_Elected_Me_as_Their_Leader/index.html'

Throws this error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 
'http://etonline.com/tv/108475_Charlie_Sheen_The_People_Have_Elec' for key 'url'

Isnt MyISAM supposed to have 1000-byte key lengths?

EDIT: There doesn't seem to be a prefix length listed on the CREATE TABLE STATUS call it looks like this:

CREATE TABLE `links` (
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(500) NOT NULL,
  PRIMARY KEY (`link_id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I tried to set one at 256 like this:

ALTER TABLE `mydb`.`links` 
DROP INDEX `url`, ADD UNIQUE INDEX `url` (`url`(256) ASC) ;

and I got the following error:

ERROR 1062: Duplicate entry '<...64-byte key...>' for key 'url'
SQL Statement:
ALTER TABLE `mydb`.`links` 
DROP INDEX `url`, ADD UNIQUE INDEX `url` (`url`(256) ASC)

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'links' already exists

I think the fallback is just because I ran the ALTER TABLE through MySQL Workbench.

Answer 1

I think the error message is only showing you the first 64 characters, but that doesn't mean the constraint is limited to 64 characters.

If your SHOW CREATE TABLE output is accurate, then the index is on all 500 characters, and you are hitting an exact duplicate.

Answer 2

When you create an index that only uses a prefix (by specifying the length of the index), then the prefix can be up to 1000 bytes (see 7.5.1 Column Indexes). Use SHOW CREATE TABLE to find out the actual length of the index.