indexing error - key length not specified

------=_NextPart_000_0108_01C74C73.B27A2D90
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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..?






------=_NextPart_000_0108_01C74C73.B27A2D90--

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..?
>
>
>
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: indexing error - key length not specified

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@raincross-tech.com]
Sent: Friday, February 09, 2007 6:29 PM
To: John Pillion
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] 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..?
>
>
>
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: indexing error - key length not specified

--------------060503020807010000000003
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

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@raincross-tech.com]
> Sent: Friday, February 09, 2007 6:29 PM
> To: John Pillion
> Cc: php-db@lists.php.net
> Subject: Re: [PHP-DB] 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..?
>>
>>
>>
>>
>>
>>
>>
>>
>
>

--------------060503020807010000000003--