SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist stef25's Avatar
    Join Date
    Nov 2004
    Location
    belgium
    Posts
    465
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating an index on mediumtext field = error

    Im trying to add an index to one of my columns, by adding the following line to my table creation statements, Description being the name of the column: INDEX idx_descr (Description)

    the line for that column in the creation statement is:
    `Description` mediumtext NOT NULL

    this gives the following error:

    #1170 - BLOB/TEXT column 'Description' used in key specification without a key length

    afaik one doesnt specify a length for mediumtext fields? Is it not possible to put an index on a mediumtext field?
    I need someone to protect me from
    all the measures they take in order to protect me

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are correct, when you define any of the TEXT datatypes, they do not require a length

    however, creating an index on them does

    that's because an index on a column umpteen bytes wide isn't going to function well

    so you are allowed to index only the leftmost x columns

    try to set x as low as you can but large enough to provide good disrcimination between rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist stef25's Avatar
    Join Date
    Nov 2004
    Location
    belgium
    Posts
    465
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, what do you mean with "left most"? should i define a length for this column? it contains a product description of about 200 words, i think mediumtext is the most appropriate data type for this, so hard to find a way around this problem
    I need someone to protect me from
    all the measures they take in order to protect me

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look up the CREATE TABLE or CREATE INDEX syntax in the manual

    both pages have examples of how to do it
    Code:
    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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