Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: indexing a long string

    I have a varchar column 1000 characters in length but am unable to create an index on it. also i need to make this column unique aswell. does a fulltext index allow me to make this column unique aswell?If so whats the syntax?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fulltext indexes are not unique-able

    try declaring your (normal) index on only a portion of the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    fulltext indexes are not unique-able

    try declaring your (normal) index on only a portion of the column
    whats the max number of characters i can index?
    whats the the syntax to declare the above (portion of column) and to make it unique? Please note the entire string needs to be unique not just a portion of it.

    e.g. I have the following:

    pd_name varchar(1000)

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    How about you check out the MySQL documentation to find out

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Incidently, what is this column? What's in it? What does it represent? Why does it need to be 1000 varchars long?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and why would uniqueness need to be determined down to the 1000th character? surely if two values are different, they will be different in the first X characters...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    and why would uniqueness need to be determined down to the 1000th character? surely if two values are different, they will be different in the first X characters...
    not for the data am storing in it! The column contains smiles strings these are string notation of a molecular structure and can be the same even up to the 999th character.

    how would you determine uniqueness on this?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii
    how would you determine uniqueness on this?
    me? i would break it up into several columns based on codons or telomeres, and define a single UNIQUE index on those columns

    alternatively, i would upgrade to at least 4.1.2, because in that release, index prefixes can be as large as 1000 bytes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •