[PHPwestoz] [OT] MySQL UNIQUE INDEX

[Mark Hendley]

Hmm. Having thought about your issue a bit, I find I have a reservation
:)

If the FID column is used, as you indicate, to store a 'path' then it is
likely that the left-hand side (the first X characters) will contain a
lot of duplication. Because you can only create and index of the 1st X
(varies based on MySQL version) characters, this makes this approach
problematic.

You'd be better of having a column that is an MD5 hash of the full path
names. This will take a max of 32 characters CHAR(32) AND will be more
efficient to boot.

To do a search with a path aspect involved, just MD5 the source path you
are looking for and search for that MD5 string. Indexing should be
quicker as well. The database will also be smaller and, with the absence
of the TEXT column, quicker generally.

FYI - you'd have about a 1 in 100 million chance of a KEY collision
using this method. If critical, you could always do a search for an
existing MD5 KEY before you insert a new one (ie: Search for it) - this
will, at least, allow you a warning that a key collision may have
occurred.

Cheers

-----Original Message-----
From: phpwestoz-admin at lists.linux.org.au
[mailto:phpwestoz-admin at lists.linux.org.au] On Behalf Of Timothy White
Sent: Monday, 21 February 2005 11:42 AM
To: phpwestoz at lists.linux.org.au
Subject: [PHPwestoz] [OT] MySQL UNIQUE INDEX

I know this isn't PHP but hopefully one of you guys will know the answer

(and it is for a PHP project)
I am trying to create a table that has a primary key and a unique key 
based on different columns.
The query is:
CREATE TABLE library (
 ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
 Filename TEXT NOT NULL,
 Artist TEXT NULL,
 Title TEXT NULL,
 Album TEXT NULL,
 FID TEXT(40) NOT NULL,
 PRIMARY KEY ID (ID),
 INDEX UID (FID)
) TYPE = MyISAM COMMENT ='';
And the error is:
ERROR 1170 (42000): BLOB/TEXT column 'FID' used in key specification 
without a key length
I can't find anything in the manual that tells me how to get around
this.

Thanks

Tim

-- 
Tim White - Use the Fox, Luke!
PGP/GPG id: 602E944D, Pub Key Serv: subkeys.pgp.net
Fingerprint: 04C2 9682 B7B2 3006 009D  A9F3 067E EDCD 602E 944D
Hi! I'm a .signature virus! Copy me into your ~/.signature to help me
spread!
--


_______________________________________________
PHPwestoz mailing list
PHPwestoz at lists.linux.org.au
http://lists.linux.org.au/listinfo/phpwestoz