SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    indexing TEXT data type while creating table

    i want to store jokes, with rating and authors' name/URL.

    It contains

    1) the JOKE where i stored as TEXT data type:--
    Code:
    joke TEXT NOT NULL default ''
    2) author name or original URL where i found the joke:--
    Code:
    from_who VARCHAR(255) default NULL,
    3) rating of the joke from 1 to 4, hence INT data type:--
    i) i need total number of visitors that rate the joke
    -
    Code:
    total_visitor INT(20) UNSIGNED NOT NULL default 0
    ii) total rating(A rates 2, B rates 1, C rates 4, so total rating is 2 + 1 + 4 = 7)
    -
    Code:
    total_rating BIGINT(20) UNSIGNED NOT NULL default 0
    iii) Average rating(example with above A,B,C 7/3 = 2.3333... rounded to 2)

    -
    Code:
    ave_rating TINYINT(1) UNSIGNED NOT NULL default 0
    4) I need to pick randomly one of the joke from the table, so i assigned a key
    -
    Code:
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT
    Ok, when a joke is inserted, I must check for duplicated joke,

    PHP Code:
    //check if joke already there
    $sql mysql_query("SELECT joke,from_who FROM table_joke WHERE joke='$theJoke' AND from_who='$fromURL'");

    if (
    mysql_num_rows($sql) == 0){
        
    mysql_query("INSERT INTO table_joke VALUES('','$theJoke','$fromURL','','','')");

    now you get the idea, putting all and create a table and i want to index these fields, 'joke' as TEXT and 'ave_rating' as TINYINT

    Code MySQL:
    "CREATE TABLE table_joke (
      id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      joke TEXT NOT NULL default '',
      from_who VARCHAR(255) default NULL,
      total_visitor INT(20) UNSIGNED NOT NULL default 0,
      total_rating BIGINT(20) UNSIGNED NOT NULL default 0,
      ave_rating TINYINT(1) UNSIGNED NOT NULL default 0,
      INDEX(joke,ave_rating),
      PRIMARY KEY(id))TYPE=MyISAM";

    but instead of installing the table, i got "BLOB/TEXT column 'joke' used in key specification without a key length" error message??

    giving
    Code:
    joke TEXT(65535) NOT NULL default ''
    will produce same error.

    how can i index TEXT data type with Unicode support??

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    TEXT columns are too big to index, you can only specify a portion of the column, measured in bytes from the left

    you don't do this in defining the TEXT column, you do it when defining the index

    by the way, FROM is a reserved word, you might want to change that column name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    TEXT columns are too big to index, you can only specify a portion of the column, measured in bytes from the left

    you don't do this in defining the TEXT column, you do it when defining the index

    by the way, FROM is a reserved word, you might want to change that column name
    yup just noticed FROM as reserved word.

    but i don't understand what you mean by 'don't do this in defining the TEXT column, you do it when defining the index'

    or will it be perfectly fine without indexing the TEXT column? i learned when we have 'WHERE' clause we should index that field.

    Code:
    "SELECT joke,from_who FROM table_joke WHERE joke='$theJoke' AND from_who='$fromURL'"
    how about if we hv millions of records and without indexing 'joke' column?

  4. #4
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    or should i use Partial Index:--

    Code MySQL:
    CREATE INDEX joke_index ON table_joke(joke(50))

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by leelong View Post
    but i don't understand what you mean by 'don't do this in defining the TEXT column, you do it when defining the index'
    you tried this in your CREATE TABLE --
    Code:
    joke TEXT(65535) NOT NULL default ''
    which attempts (incorrectly) to define a length for a TEXT column (presumably this got a syntax error)

    you would define the index as something like this --
    Code:
    ALTER TABLE table_joke
    ADD INDEX joke_ix ( joke(255) )
    note that you can index no more than the leftmost 255 bytes

    which is probably not suitable for your purposes

    a FULLTEXT index would be much better

    by the way, you do know how to look this stuff up in the manual, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you tried this in your CREATE TABLE --
    Code:
    joke TEXT(65535) NOT NULL default ''
    which attempts (incorrectly) to define a length for a TEXT column (presumably this got a syntax error)

    you would define the index as something like this --
    Code:
    ALTER TABLE table_joke
    ADD INDEX joke_ix ( joke(255) )
    note that you can index no more than the leftmost 255 bytes

    which is probably not suitable for your purposes

    a FULLTEXT index would be much better

    by the way, you do know how to look this stuff up in the manual, right?

    FULLTEXT(joke) ??

    i dont read manual but googled.

    will FULLTEXT indexing takes more HDD spaces??

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by leelong View Post
    i dont read manual but googled.
    not a good idea

    go to mysql.com and download the documentation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    whatever...

    my final quest is, is it worth the extra HDD space where all i need is JUST to check for duplicated joke.

    and hence

    PHP Code:
    //check if joke already there
    $sql mysql_query("SELECT joke FROM table_joke WHERE joke='$theJoke'");

    //if not duplicated then insert the joke
    if (mysql_num_rows($sql) == 0){
        
    mysql_query("INSERT INTO table_joke VALUES('','$theJoke','$fromURL','','','')");


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    FULLTEXT indexing will not stop duplicate content

    in fact, if the joke is, like, a couple hundred words, then even one misplaced extraneous comma in the text is going to make it different

    for this type of duplication, your best bet is to actually read the jokes yourself

    "hmmm, i've heard this one before"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    where can i buy e-book version of 'Simply SQL' ??

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by leelong View Post
    where can i buy e-book version of 'Simply SQL' ??
    http://www.sitepoint.com/books/sql1/

    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
  •