CODEWITHSUNDEEP
mysqltext
Hoytman
Hoytman

Reputation: 1812

Would it be effective to store text of different sizes in different mysql tables?

I am building a database system that will be storing large amounts of text.

The text will be stored in a table with an id column and one varchar/text column.

I was wondering if it would be more effective to use a single table which employed a large varchar, or multiple tables, each employing a different text type.

The multiple table option would contain several different tables, each employing a different kind of text (tinytext, text, etc.), and the system would store text in the most appropriate one based on the length of the text.

I am concerned with both speed and storage space, and would like a solution to balances both.

Edit -

The text table will not be searched on, but it may be joined (usually an id number will be determined, then a single row accessed).

Size will typically be smaller that text, but some will be large enough to require mediumtext. I doubt that longtext will be needed.

Upvotes: 0

Views: 102

Answers (1)

O. Jones
O. Jones

Reputation: 108641

Keep it simple! Seriously.

Unless you have an overwhelming majority of text items that are 255 characters or shorter, just use TEXT or LONGTEXT. Spend your time doing interesting things with your text, not fiddling around with complex data structures. Get your project done now; optimize later.

Disk drives and RAM are getting cheaper much faster than your time is.

If your app requirements absolutely need you to use varchar data, for its brevity and searchability, instead of text data, you can do the following.

Create an article table, with one row per text article. It will have all the stuff you need to manage an article, including let's say the title, author, and an article_id.

Create a second table called something like article_text. It will have, possibly, four columns.

  article_id    foreign key to article table.
  language      a language code, if you happen to store translations of articles
  ordinal       a sequence number
  textfrag    varchar(255)  part of the text.

Store each article's text in an series of article_text rows with ascending ordinal values. Each textfrag will hold up to 255 characters of your text. To retrieve an article's text you'll use a query like this.

 SELECT textfrag 
   FROM article_text
  WHERE language = 'en_US'   /* or whatever */
    AND article_id = 23456   /* or whatever */
  ORDER BY ordinal

Then, you'll fetch a bunch of rows, concatenate the contents of the textfrag items, and there's your article with no effective length limit. If you create an index with all the fields in it, your retrieval time will be really fast because all retrievals will come from the index.

  (article_id, language, ordinal, textfrag)

If you do your best to spit the text into fragments at word boundaries, and you use MyISAM, and you use FULLTEXT indexes, you'll get a very effective fulltext search system.

Upvotes: 1

Related Questions