Database creation for articles

im trying to create database (mysql) for articles which is assumed to have at least 5000-6000 words.

  1. i have been using varchar(255), which should field should create text, medium text ,long text and what length should define like i define varchar(255)

  2. what will happen if leave is field empty like medium() will it take the max value…

TEXT, TINYTEXT, MEDIUMTEXT and LONGTEXT are essentially supersized VARCHARS in they will only allocate the space needed to store the data, plus 2-4 bytes of overhead. LONGTEXT requires the most overhead (4b), TEXT and TINYTEXT the least with (2b).

Which you use depends on how much data you need to store.

  • TINYTEXT holds up to 255 characters (255b) - essentially VARCHAR(255)
  • TEXT holds up to 64Kb, or 65,000 characters
  • MEDIUMTEXT holds up to 64MB
  • LONGTEXT holds up to 4GB
1 Like

MySQL is not the best solution for document based data storage. If you have the choice I would prefer noSQL or MongoDB for storing articles

You will probably also want meaningful search for those articles as well. In which case I would recommend using open search as a double edged sword for storage and search as a NoSQL solution.

Bonsi provides a free dev/test open search cluster to try out open search without much hassle. Similar to something like Atlas for MongoDB.

thank for the suggestion but i have been using Mysql from years, i have been using it for wordpress too, so i cant shift over night to other, can kindly help with my need with mysqli for now, in future i will definitely move to your suggestion

Another approach is to store the articles as files and have the database reference the files, rather than store them.

Do what DaveMaxwell suggested. Stuff it all into a longtext column since you are married to MySQL.

There is another option NoSQL but without search capabilities. Store the documents on a CDN and retain a relationship in the database via a guid or some other unique identifier. Which is an extension of what sam74 but preventing clogging up an app server with the files.

If he’s got 5,000-6,000 words per article, LONGTEXT is overkill. TEXT will work 90% of the time

6,000 words * 10 letters (avg # of letters in a English word is 5 so 10 should account for other languages) = 60,000 letters. The allows for another 1,000 numbers, spaces and punctuation marks before it wouldn’t fall under the 65,000 character limit that TEXT has.

Now if there is HTML markup embedded in the articles, then all bets are off, and MEDIUMTEXT or even LONGTEXT might be required.

Useless Factoid of the day

Finnish supposedly has the largest number of average characters per word at just under 8.

Lets not forget about the MySQL Document Store that utilizes a JSON data type.

“MySQL stores JSON documents in an internal format that allows quick read access to document elements. The JSON binary format is structured in the way that permits the server to search for values within the JSON document directly by key or array index, which is very fast.”

A single database instance is never going to be as performant and reliable as a distributed data model that can auto scale like MongoDB and Atlas. Very fast is an arbitrary measurement on a server that can only handle so much concurrency, load, memory before performance is impacted. Although it can be done stuffing document models into a relational database seems like a dirty, lazy, cope out given all the other more appropriate NoSQL innovations available. However, I think in terms of articles this thread is about storing large amounts of body text not necessarily unstructured document objects.