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
RE: [PHP-DB] 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:[EMAIL PROTECTED] 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: [PHP-DB] indexing error - key length not specified
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:[EMAIL PROTECTED] 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..?
