#1170 - BLOB/TEXT column 'other' used in key specification without a key length

[mmelen]

Can anyone make sense of the Myql Query? I am trying to make a simple table - any idea why I get that error?


Error

SQL query:

CREATE TABLE IF NOT EXISTS `melennet_proxykey`.`pages` (
`main` VARCHAR( 64 ) NOT NULL ,
`other` TEXT NOT NULL ,
`article` BLOB NOT NULL ,
PRIMARY KEY ( `main` ) ,
INDEX ( `other` , `article` ) ,
FULLTEXT (
`main` ,
`other` ,
`article`
)
) ENGINE = MYISAM

MySQL said: Documentation
#1170 - BLOB/TEXT column 'other' used in key specification without a key length

[mmelen]

I even tried:

CREATE TABLE `melennet_proxykey`.`pages` (
`main` VARCHAR( 64 ) NOT NULL ,
`other` TEXT( 255) NOT NULL ,
`article` BLOB( 65535) NOT NULL ,
PRIMARY KEY ( `main` ) ,
INDEX ( `other` , `article` ) ,
FULLTEXT (
`main` ,
`other` ,
`article`
)
) ENGINE = MYISAM

[r937]

i believe the problem is here --

`other` TEXT( 255) NOT NULL ,
`article` BLOB( 65535) NOT NULL ,
INDEX ( `other` , `article` )

the maximum prefix length is 255 (or 1000 bytes for MyISAM and InnoDB tables as of MySQL 4.1.2), and you have article's prefix as 65K

if you think about it for a second, 100 oughta be plenty

a regular INDEX, as opposed to a FULLTEXT index, is used to find a particular row, based on matching data values from left to right

to have an index on (other,article) would be useful only if you had a WHERE clause like this --

WHERE other = 'a specific value which exactly equals the value in the column for exactly 100 bytes'
  AND article = 'another string which ... well, you get my point, yeah?'

[mmelen]

got it working:

CREATE TABLE `melennet_proxykey`.`proxykey` (
`id` MEDIUMINT( 5 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
`main` VARCHAR( 64 ) NOT NULL ,
`other` TEXT NOT NULL ,
`article` BLOB NOT NULL ,
`type` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

Thanks!

[r937]

you've abandoned the FULLTEXT index as well as the INDEX?

yep, that'll certainly get rid of that pesky error message you were getting



but how do you plan to search this table?

and shouldn't you have a UNIQUE inde on the main column? after all, it was your PK for a while

also, by the way, what's the type column for?