Database design (MySql)::should we put html data in text field inside database table or more efficient to save inside text.txt file?

Question

Hi, We building big Web Application and we use mysql, we want to make mysql database more fast. Some of us think if we will put message html body inside table and not inside text.txt in will make database heavy and not fast. Thanks,

*Part of main table that hold message: option 1:hold html message body inside database

message {
id (int)
subject (varchar)
body (text)
}

option 2: hold html message body inside body1.txt file

message {
id (int)
subject (varchar)
file_body_path (varchar)
}

*

Answer 1

If you:

1. Don't need transactional control over the contents of your files and
2. Only treat the files as an atomic entity (i. e. don't parse them, search for their content etc.)

, then you better store them out of the database.

The HTTP server will serve the disk-based files much faster to begin with.

Answer 2

As Quassnoi correctly points out, the webserver will most likely be faster serving txt files than data from the DB ...

BUT: This only works if the webserver doesn't have to run any searches/queries against the DB to build the links between the TXT files.

Think of these use cases:

• remove a text file
• add a text file
• add a link to a text file
• remove a link from a text file
• find a text passage within a text file.

each of these use cases will require your parsing the TXT file and maintaining all the needed links in the 'index-pages'. How will you do this in your content management system?