tinytext as a key

m3rajk posted this at 16:45 — 26th August 2003.

Joined: Jul 2003

i'm looking at my oreilly book and don't see any examples of declating a length for tinytext. i used it specifcally because it can be up to 255 and i dind't think i needed to. while that's possible to change for a fotrum title, i would like to keep it for a thread title in that table instead of using varchar(255)

Quote: Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.56

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use findyourdesire
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------+
| Tables_in_findyourdesire |
+--------------------------+
| bio |
| comments |
| friends |
| interests |
| pests |
| stats |
| tops |
| users |
| votes |
+--------------------------+
9 rows in set (0.33 sec)

mysql> CREATE TABLE forums(
-> forum_id tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
-> title tinytext NOT NULL,
-> blurb tinytext,
-> access tinyint unsigned NOT NULL,
-> threads smallint,
-> last_post_auth varchar(15) NOT NULL,
-> last_post_time time default '00:00:00' NOT NULL,
-> INDEX title (title)
-> ) TYPE=MyISAM;
ERROR 1170: BLOB column 'title' used in key specification without a key length
mysql>

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

Mark Hensler posted this at 17:02 — 26th August 2003.

He has: 4,048 posts

Joined: Aug 2000

I recommend using varchar(255) over tinytext.

varchar can be index easily, and var-char is short for variable length character field. Meaning a varchar(255) isn't always 255 bytes long. A varchar(255) may store up to 255 bytes of data. The actuall field length is 1 byte plus the length of the data stored. (I believe the extra byte preceeds the data and is read by mysql as an integer representation of the length of the data stored, or an offset for the next field)

A char() field, on the other hand. Is a fixed length field. A char(255) will always be 255 bytes long, irregardless of the length of data being stored in it.

To index off of text or blob fields, you need to specify how many bytes into the field mysql should use for the index. This is because it is very inefficient to index the whole field (especially when we're talking kilobyte, megabyte, or even gigabyte field lengths).

CREATE TABLE forums(
  forum_id tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
  title tinytext NOT NULL,
  blurb tinytext,
  access tinyint unsigned NOT NULL,
  threads smallint,
  last_post_auth varchar(15) NOT NULL,
  last_post_time time default '00:00:00' NOT NULL,
  INDEX title (title(25))
) TYPE=MyISAM;

Mark Hensler
If there is no answer on Google, then there is no question.

Mark Hensler posted this at 17:03 — 26th August 2003.

He has: 4,048 posts

Joined: Aug 2000

Oh, and I'd throw out that O'Reilly book.

m3rajk posted this at 20:14 — 26th August 2003.

They have: 461 posts

Joined: Jul 2003

yeah. the php books seem to help but i don't like the mysql one. too many things i can't find. i'm about to go off to the mysql website and see if they describe floats better. i want to keep gmt offset and since it can range from -12 to +12 with one number being somethig like 5.75 (have the exact elsewhere) a float is needed to keep that unless i use a small int and multiple everything by 100, which is something i'm wondering if it'd be beter to do since they are half the size of a float. i'm not sure if i should call it with float(4,2) or float(3,2) since sql will probably look at it as -12.00 to +12.00

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

m3rajk posted this at 20:46 — 26th August 2003.

They have: 461 posts

Joined: Jul 2003

and from mysql's online manual....

Quote: The FLOAT type is used to represent approximate numeric datatypes. The SQL-92 standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification. When the keyword FLOAT is used for a column type without a precision specification, MySQL uses four bytes to store the values. A variant syntax is also supported, with two numbers given in parentheses following the FLOAT keyword. With this option, the first number continues to represent the storage requirements for the value in bytes, and the second number specifies the number of digits to be stored and displayed following the decimal point (as with DECIMAL and NUMERIC). When MySQL is asked to store a number for such a column with more decimal digits following the decimal point than specified for the column, the value is rounded to eliminate the extra digits when the value is stored.

so i want float(5,2) .. hmm.. wonder if it'd be better to do the multiplication thing instead... look like that'd be beneficial for storage, but i think i'll go with float since it's easier to use in the actual application if i just pull it out as is.unlesssomone has some comment on this about something i'm missing.

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

Mark Hensler posted this at 06:42 — 27th August 2003.

He has: 4,048 posts

Joined: Aug 2000

I found the explanation you quoted to be a bit confusing. Especially this part: "first number continues to represent the storage requirements for the value in bytes" I'm not sure how floats are stored, so I can't calculate the number of bytes required.

But after reading the FLOAT(precision) and FLOAT(M,D) descriptions (here: 6.2 Column Types), FLOAT(5,2) sounds right.

Mark Hensler
If there is no answer on Google, then there is no question.