1

Is it a good idea to use varchar if I have so store data of length 1000? Below is my script and column SR_NOTES is the one I a worried about. I user utf8 and the engine is INNODB. Would it be wiser to go with say for example, TINYTEXT? I am keen to create a database design without hampering performance. 

CREATE TABLE IF NOT EXISTS SROR (
 SROR_ID INT UNSIGNED NOT NULL,
 CRAN_ID INT UNSIGNED NOT NULL,
 SR_TAGS VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL, 
 GR_VALUES DECIMAL(4,2), -- 12.25 R
 NOTICE_PERIOD SMALLINT UNSIGNED DEFAULT NULL,
 C_STATUS VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT 'INACTIVE', 
 SR_NOTES VARCHAR(1000) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT NULL, 
 SR_CONTROL VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT NULL, 
 START_DATE DATETIME DEFAULT CURRENT_TIMESTAMP,  
 LAST_UPDATE DATETIME ON UPDATE CURRENT_TIMESTAMP, 
 PRIMARY KEY (SROR_ID, CRAN_ID),
 INDEX idx_SROR_id1 (SROR_ID ASC),
 INDEX idx_SROR_id2 (CRAN_ID ASC),
 CONSTRAINT fk_SROR_C1
  FOREIGN KEY (CRAN_ID)
  REFERENCES R_CONFIG (CRAN_ID)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
Jonnix
  • 4,121
  • 1
  • 30
  • 31
user4826347
  • 783
  • 2
  • 11
  • 29
  • 1
    255 is the maximum character length of a varchar. I would just use TEXT, not even tinytext. It will not impede performance of the database unless you are trying to index on these text blocks. – skrilled Jun 04 '15 at 22:53
  • `varchar` length is `255 only` so you need to must go for `tynitext`. tanks – Alive to die - Anant Jun 04 '15 at 22:54
  • 2
    actually the varchar limit in mysql is `65,535` not `255` as suggested. To answer your question, can actually be answered by this [question](http://stackoverflow.com/questions/2023481/mysql-large-varchar-vs-text) – Jonathan Jun 04 '15 at 22:58
  • 1
    The max length of an index is 255 but `varchar` can have more than 255 characters (since MySQL 5.0.3) If you don't need more then you can use it. – juergen d Jun 04 '15 at 22:59
  • TINYTEXT can't hold 1000, TINYTEXT is 2^8, TEXT is 2^16 limit. VARCHAR can. The performance between `TEXT` and `VARCHAR` for 1000 characters would be negligible in my opinion there is no reason to think this hard into it. – Devon Bessemer Jun 04 '15 at 23:20
  • Another correction -- InnoDB has a limit of 767 _bytes_ per column. That is like `VARCHAR(255) utf8` of `VARCHAR(191) utf8mb4`. The limit can be changed. – Rick James Jun 08 '15 at 23:49
  • Another correction -- Temp tables during a `SELECT` try to use `MEMORY` (which is better than `MyISAM`), but can't if you have a `TINYTEXT`. So, that is a case where the otherwise equivalent `VARCHAR(255)` is better. – Rick James Jun 08 '15 at 23:51
  • Inclusion of `SR_NOTES VARCHAR(1000)` in a `SELECT` that needs a tmp table will probably eliminate the use of `MEMORY`. (Check the details.) – Rick James Jun 08 '15 at 23:57

1 Answers1

0

Consider using a different data type, eg TEXT can store up to 65535 characters. A good alternative for storing notes.

See: What is the MySQL VARCHAR max size?

Community
  • 1
  • 1
angelcool.net
  • 2,505
  • 1
  • 24
  • 26
  • 1
    I just read that using TEXT can have impact on performance as it creates a temp table, unlike varchar. If both VARCHAR and TEXT can both store data of, say, length 1000 and if TEXT uses additional resources, why would one prefer TEXT to VARCHAR? – user4826347 Jun 05 '15 at 00:26
  • How big is your database? How many records are you talking about? Are you using indexes properly ? If you are, I will dare to say you won't notice the difference. Also, when you say performance do you mean data-lookup time or max number of connections or both? IMHO You should start worrying about TEXT vs VARCHAR performance when you have millions, hundreds of millions or billions of records, or when your queries are becoming slow. Then again, I don't know your circumstances. If learning is what you want you're in the right path. Getting experienced is good :) – angelcool.net Jun 05 '15 at 03:52
  • I am creating a PHP website. Optimistically thinking, I expect pretty good traffic. And I want to create a solution with that visionary thinking. So I am putting efforts on attention to detail. By performance I meant both concurrency and lookup time which I believe will constitute user experience. – user4826347 Jun 05 '15 at 09:00
  • Visionary thinking is good. Usually for high traffic sites I would consider using MySQL together with other pieces of software, like _memcached_ which is used to cache the data returned by a query, it helps reduce the number of connections to your DB server. Also, consider NoSQL solutions too. I'm currently using _riak_ to store MySQL data, the lookup time in that thing is great, it's setup with a cluster of 5 nodes to handle a good amount of connections. – angelcool.net Jun 05 '15 at 16:29
  • For visionary thinking in MySQL, see [Rick's RoTs (Rules of Thumb)](http://mysql.rjweb.org/doc.php/ricksrots). It was developed from years of large PHP websites, and many other applications. – Rick James Jun 08 '15 at 23:55