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
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
`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?'
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