LongText and web performance?

Hi all,

Just a quick Q. If I store content for html pages within medium/longtext fields within a mysql table naturally the size of the table will grow as more pages are added. But my question is, storing a page worth of text for a couple hundred pages will it impact on web access performance?

I'm looking into the idea of storing ALL my web related info within mysql database (sort of challenge :) but at the same time I don't want to create a snail system.

Anyone know where I can find info on access performance of mysql databases based on their size...or does anyone have any advice/ideas of their own?

Thanx
Ben

A popular approach on many web sites is to split long texts up into pages. There are several ways to accomplish this, and unfortunately I am not aware of any web resources on the topic - I figured it out the long way :(

Note that the long text still reside only in one field, but with some string manipulation functions you can set virtual page breaks in your text. There are essentially only one fundemental issue, and that is if your text includes HTML formatting or not.

If your text does NOT include HTML formatting, virtual page breaks are easy, as you break simply on line number count. If your text does contain HTML formatting, you need to be aware that the HTML can break stuff, if split over pages, so you really need to figure a way to deal with it. This is difficult, because it depends on what HTML you would allow, and what not.

All I can add, is that you could use either cookies, of a clever constructed GET request ( my fav ) to instruct the application that you are moving forward, or backward in pages.

You will also see some sites offer a 'printer firendly' version, which is 9/10 times the complete text on one page, approx. 72 characters wide ( use tables for this ) and the minimum of eye candy ( graphics etc. ).

Hope these guidelines can help you somewhat.

Cheers

Hey,

Howz the weather in Jo'burg? Ironically, I come a third of the way around the world and end up getting help from the country I left :) Howz that for irony!!

Thanx for your reply. Sounds good. I was planning on leaving out the html tags, as I want to use a standard template and replace the contents of a single cell with the contents within he MySQL database.

I plan on being quite succint with the quantity of info since it's got to fit on one page (the user will then be able to click through the pages that make up the subject.

The location of images will fit into set cells as well...

My other alternative was having a whole lot of text files and using SSI / Perl to load them and set any formatting outside the html template...

I just wanted to make sure that the medium/longtext fields wouldn't slow the system down unnecessarily...I'm a stickler for quick load times.

Thanx again.

Quote:

---

Irony is when a woman stops telling you how women are just as effective as
men in combat just long enough to make you kill a spider for her.
-- From a Mailing List

---

Now, back to business...

Your idea about the text files might actually work better - along the same reasons why we don't normally store image BLOB's in a DB. If you have full control over the physical hardware, you can really do some neat tricks to speed up the disk IO and overall performance.

You can still use the DB to store reference info about the text files, and perhaps even the keyword index for searches.

The best way would of course be to test, and test under load. You could write a simple script to generate large random text blocks, and work with that as the data - unless you already have some test data :)

You then need to run several GET requests from a different machine (or machines) to load the data. You can then measure the download time (local) and see for yourself which is faster. You can also, in this environment, measure system stats, like CPU, RAM and Disk IO.

Desinging for performance and scalability is sometimes much more difficult, but testing different approaches like this is essential - and I do it all the time. It's sometimes easier - in fact - always easier to just hack a solution and go with it, regardless if it's optimized for performance or not. In the end, though, the extra time one puts into designing a highly optimized solution makes it all worth it.

Cheers