Perhaps I should of spoke more exactly. In MySQL 5.0 you can index
these, but the maximum index length is limted by the storage engine. So
you have to be specific as to how you index these columns.
The deal is that with large column fields making an index of several
thousand characters (for example) doesn't help you that much, the index
gets as big as the field (comparatively) and the performance gains from
having an index is marginal, as I understand it. (an expert is free to
jump in at this point)
So one might want to analyze why you're indexing this field at all.
Perhaps something specialized like a fulltext index is more appropriate.
Take a look at this page in the docs:
http://dev.mysql.com/doc/refman/5.0/en/indexes.html
It explains it somewhat. You can as it mentions specify the index size
which the db should take, but you'll be indexing a subset of the actual
data with the col_name() style of statement.
In my opinion, you might want to take a look at why you want to index
such a large column, I don't see a reason myself unless it's something
like a fulltext index.
I hope that helps,
-Micah
On 02/09/2007 04:59 PM, John wrote:
Micah,
You can't use that column type as an index because it's variable length.
Makes sense.
Make it a varchar or something that's definite to index it.
It is possible the value stored can reach up to about 2k characters, which
is too long for a varchar type. Any recommendations as to what type to use
that wouldn't be variable length, but be able to store strings/values of
that size?
Thanks!
John
-----Original Message-----
From: Micah Stevens [mailto:micah@xxxxxxxxxxxxxxxxxx]
Sent: Friday, February 09, 2007 6:29 PM
To: John Pillion
Cc: php-db@xxxxxxxxxxxxx
Subject: Re: indexing error - key length not specified
You can't use that column type as an index because it's variable length.
Make it a varchar or something that's definite to index it.
-Micah
On 02/09/2007 03:56 PM, John Pillion wrote:
I am trying to set an index on a field in my table, but am getting the
following error:
BLOB column 'ReadBy' used in key specification without a key length
The fieldtype is blob, though I get the same error whether blob or text,
or
the medium and long versions of each. My first though, based on the
error,
was I needed to set a length to the field - but it won't accept/store any
length I give it (because it's a variable length?)
I'm using mysql on from 1and1 (hosting provider), though I don't know what
version they're running
.. any thoughts?
As an alternative, how much less efficient would it be to do a ".LIKE
'%mystring%'" versus a MATCH..?
