Jump to content


Photo

Problems creating full text index


  • Please log in to reply
5 replies to this topic

#1 watts

watts
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 30 October 2006 - 12:34 AM

Hello,
I am trying to add a full-text search to my site but when I tried to create a full-text index in my db I got the message, "#1071 - Specified key was too long; max key length is 1000 bytes"

Can anyone tell me what this means and how to deal with it?

Any suggestions would be greatly appreciated.


#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 30 October 2006 - 01:16 AM

The problem is pretty much what it says.

Have you tried googling it before posting:

http://www.xaprb.com...ength-in-mysql/

http://bugs.mysql.com/bug.php?id=4541

http://forums.mysql....?25,68461,68461


#3 watts

watts
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 30 October 2006 - 02:37 AM

Thanks for these links.  I've looked over them and I'm not sure what that means I should do.  I haven't gotten this error in the process of creating the table in the first place only when I try to create a full-text index.  I tested and I can see that when I only put one field in the index and it's a short field I don't get the error. The question is, what if I want to do full-text search with multiple fields involving a larger field?  Is this impossible?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 October 2006 - 05:03 PM

Post your table structure.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 watts

watts
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 31 October 2006 - 08:28 PM

I started with the following:

imgID - int(8)
imgURL - varchar(100)
imgThbURL - varchar(100)
imgTitle - varchar(200)
imgCaption - varchar(2000)

and when I tried to index imgCaption alone or with other fields I got the error.  I have spoken to my client and determined that I can safely set the imgCaption length to 400 and now the full text index is working.

Thanks for the links about key lengths.  I am however still interested in how you would do a full-text index if you did need the field to be much larger.

Thanks


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 October 2006 - 09:44 PM

VARCHAR(2000)? That's not even an option...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users