simple mysql performance question

I am building a very sim开发者_开发问答ple classified site.

There is a form that puts data in mysql table. Now how should this data be displayed ? Is it better to build html pages from the data in a table , and then display it to the users OR is it better to, fetch the data from the mysql table each time a user wants to see the data ? I hope I was clear!

---

Performance-wise, it's generally better to keep the static versions of the HTML pages.

However, you may have too many dynamic content which can bloat your disk space, and you should apply some extra effort to track cache expiration (which can be even more expensive than generating the content dynamically).

It's a matter of tradeoff, and to make any other advices we would need to know the nature of your data.

• If it's a blog with content updated rarely but read often, it's better to cache.

• If it's a product search engine with mostly unique queries and changing stock, it's better to always query the database.

Note that MySQL implements query cache: it can cache the resultsets of the queries and if the query is repeated verbatim and no underlying tables were changed since the last query, then it's served out of the cache.

It tracks the cache expiration automatically, saves you of the need to keep the files on the disk and generally combines the benefits of both methods.

---

You can use Php caching techniques if the data would not change frequently. Keep loading the cached contents for frequent visits.

http://www.developertutorials.com/tutorials/php/php-caching-1370/

---

Use both, via a caching mechanism. Based on parameters, the page would be re-rendered (has not been viewed in X time or at all) or displayed from cache otherwise.

As stated though, it depends heavily on the amount of and frequency with which the data is accessed. More information would warrant a more detailed response.

---

It depends on a few things. Ask yourself two easy questions:

1) How often does the content change? Are your classified ads static or are they changing a lot on the page. How much control do you want on that page to have rotating ads, comments from users, reviews etc.

2) Are you going to be VERY high traffic? So much so that you are looking at a bottleneck at the database?

If you can say "Yes, no doubts, tomorrow" to question #2, go static. even it means adding other things in via ajax or non database calls (ie includes) in order to make the page pseudo-dynamic.

Otherwise if you say "Yes" to question #1, go with a dynamic page so you have the freshest content at all times. These days users have gotten very used to instant gratification on posts and such. Gone are the days we would wait for hours for a comment to appear in a thread (I am looking at you Slashdot).

Hope this helps!

---

Start with the simplest possible solution that satisfies the requirements, and go from there.

If you implement a cache but didn't need one, you have wasted time (and/or money). You could have implemented features instead. Also, now you (might) have to deal with the cache everytime you add features.

If you don't implement a cache and realize you need one, you are now in a very good position to implement a smart one, because now you know exactly what needs to be cached.