1

I have a website that let's each user create a webpage (to advertise his product). Once the page is created it will never be modified again.

Now, my question: Is it better to keep the page content (only a few parts are editable) into a MySql database and generate it using queries everytime the page is accesed or to create a static webpage containing all the info and store it onto the server?

If I store every page on the disk, I may reach like 200.000 files.

If I store each page in MySQL database I would have to make a query each time the page is requested, and for like 200.000 entries and 5-6 queries/second I think the website will be slow...

So what's better?

XCS
  • 27,244
  • 26
  • 101
  • 151
  • 1
    consider this, what if you want to change the design of the pages, you cannot go and edit or ask all the users to edit the pages. – Manoj May 18 '11 at 11:58
  • The page can be easily redesigned. The only part that can't is the user input, that's only a small table that I can also change using CSS. – XCS May 18 '11 at 20:28

7 Answers7

3

MySQL will be able to handle the load if you create the tables properly (normalized and indexed). But if the content of the page doesn't change after creation, it's better if you cache the page statically. You can organize the files into buckets (folders) so that one folder doesn't have too many files in it.

Remember to cache only the content areas and not the templates. Unless each user has complete control over how his/her page shows up.

JohnP
  • 49,507
  • 13
  • 108
  • 140
  • Ok, so is it a good ideea: I will create a new folder every month (automaticaly) and place the next month's entries in that folder. Or when a folder is filled (eg: 10.000 files) I'll create another folder... ? – XCS May 18 '11 at 20:34
  • 1
    Depends on your use case. You could also hash the client name or some field and organize the files by the last two digits. Have a look at this question and related entries : http://stackoverflow.com/questions/671260/ – JohnP May 19 '11 at 04:16
3

200.000 files writable by the Apache process is not a good idea.

I recommend using a database. Database imports/exports are easier, not telling about the difference between the maintenance costs.

Databases are using caching, and if nothing is changed, they will pull up the last result, without running the query again. This doesn't stand, thanks JohnP.

beerwin
  • 9,813
  • 6
  • 42
  • 57
  • `Databases are using caching, and if nothing is changed, they will pull up the last result`, are you sure about that? I would be interested in finding out more about this. – JohnP May 18 '11 at 12:58
  • mysql does this, as it temporarily stores the results. I've had read something about this quiet a long ago. – beerwin May 18 '11 at 13:07
  • What about SEO ? Will the google index the content of the page if it's MySQL generated? – XCS May 18 '11 at 20:31
  • @cristy yes it will as long as you have a proper navigation or a sitemap. Google doesn't care about how you get the data. – JohnP May 19 '11 at 04:13
  • 1
    @beerwin I don't think it works like that. I'm pretty sure query caching doesn't store all the data in memory if nothing has changed. This might work if you run the same query over and over again, but wouldn't be possible if you have different queries (as in this case). – JohnP May 19 '11 at 04:22
1

If you want to redesign your webpage sometimes later you must be using MySQL to store the pages as you can't really change them (unless you dig into regexp) after making them static. About the time issue - its not an issue if you set indexes right.

Daniel
  • 30,896
  • 18
  • 85
  • 139
0

if the data is small to moderate then prefer static hardcoding ie. putting the data in the HTML, but if it is huge, computational or dynamic and changing you have no option but to use a connectivity to the Database

Ashutosh
  • 29
  • 2
0

I believe that proper caching technique with certain attributes (long exp. time) would be better than static pages or retrieving everything from mysql everytime.

sharoni
  • 158
  • 9
0

Static content is usually a good thing if you have a lot of traffic, but 5-6 queries a second is not hard for the database at all, so with your current load it doesn't matter.

You can spread the static files to different directories by file name and set up rewrite rules in your web server (mod_rewrite on Apache, basic location matching with regexp on Nginx and similar on other web servers). That way you won't even have to invoke the PHP interpreter.

Emil Vikström
  • 90,431
  • 16
  • 141
  • 175
0

A database and proper caching. 200.000 pages times, what? 5KB? That's 1 GB. Easy to keep in RAM. Besides 5/6 queries per second is easy on a database. Program first, then benchmark.

// insert quip about premature optimisation
Sander Marechal
  • 22,978
  • 13
  • 65
  • 96