Indexing TEXT data type while creating table

Databases
1

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

It contains

  1. the JOKE where i stored as TEXT data type:–
joke TEXT NOT NULL default ''
  1. author name or original URL where i found the joke:–
from_who VARCHAR(255) default NULL,

  1. rating of the joke from 1 to 4, hence INT data type:–
    i) i need total number of visitors that rate the joke

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)
-

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)

 - 

ave_rating TINYINT(1) UNSIGNED NOT NULL default 0

  1. I need to pick randomly one of the joke from the table, so i assigned a key

id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT

Ok, when a joke is inserted, I must check for duplicated joke,


//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


"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

joke TEXT(65535) NOT NULL default ''

will produce same error.

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

2

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

3

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.

"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

or should i use Partial Index:–

CREATE INDEX joke_index ON table_joke(joke(50))
5

you tried this in your CREATE TABLE –

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 –

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?

:slight_smile:

6

FULLTEXT(joke) ??

i dont read manual but googled.

will FULLTEXT indexing takes more HDD spaces??

7

not a good idea

go to mysql.com and download the documentation

:slight_smile:

8

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


//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

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” :smiley:

10

where can i buy e-book version of ‘Simply SQL’ ??

11

http://www.sitepoint.com/books/sql1/

:slight_smile: