MySQL? XML? XML and MySQL? SQLite?!

[Matthew Bates]

Hi,

I've been using PHP for a while now and I'm beginning to migrate to
PHP5. So far, I'm impressed.

I need to develop an admin application to enable users to update a
website that is built on a MySQL database. In most cases, I'd simply
develop an application to manipulate the database but as the website is
updated fairly infrequently, I am considering a number of further
options instead to alleviate server load.

I am considering retaining the MySQL database for structure and building
scripts to generate XML from the data that can be processed and
transformed server-side (XSLT). Another option is SQLite. And yet
another approach is to generate static HTML pages from the database when
it needs to be updated or published.

Based on knowledge and past experience, what would everyone recommend? I
would welcome any feedback to help me in my decisions.

Thanks in advance.

Matt

[Colin McKinnon]

Matthew Bates wrote:

Hi,

I've been using PHP for a while now and I'm beginning to migrate to
PHP5. So far, I'm impressed.

I need to develop an admin application to enable users to update a
website that is built on a MySQL database. In most cases, I'd simply
develop an application to manipulate the database but as the website is
updated fairly infrequently, I am considering a number of further
options instead to alleviate server load.

So, let's see if I've got this right. You've got a process which doesn't
happen often (so by definition can't produce a lot of load), and because it
doesn't happen often you want to do a lot of work and add a lot of
complication to it.

hmmm.

C.

[Matthew Bates]

>>I need to develop an admin application to enable users to update a

website that is built on a MySQL database. In most cases, I'd simply
develop an application to manipulate the database but as the website is
updated fairly infrequently, I am considering a number of further
options instead to alleviate server load.

So, let's see if I've got this right. You've got a process which doesn't
happen often (so by definition can't produce a lot of load), and because it
doesn't happen often you want to do a lot of work and add a lot of
complication to it.

Sorry, I don't think my original post was that clear.

The website is visited often and when I refer to server load I mean the
processing that is needed to access/query the database each time to
display the pages.

The actual updates to the content are made infrequently so I was
therefore considering the options in publishing the content.

Matt

[Chung Leong]

"Matthew Bates" <ma********@hotmail.com> wrote in message
news:tG*************@text.news.blueyonder.co.uk...

I need to develop an admin application to enable users to update a
website that is built on a MySQL database. In most cases, I'd simply
develop an application to manipulate the database but as the website is
updated fairly infrequently, I am considering a number of further
options instead to alleviate server load.

So, let's see if I've got this right. You've got a process which doesn't
happen often (so by definition can't produce a lot of load), and because it doesn't happen often you want to do a lot of work and add a lot of
complication to it.

Sorry, I don't think my original post was that clear.

The website is visited often and when I refer to server load I mean the
processing that is needed to access/query the database each time to
display the pages.

The actual updates to the content are made infrequently so I was
therefore considering the options in publishing the content.

Maybe something like jpcache (http://www.jpcache.com/)?

[Joe Webster]

"Matthew Bates" <ma********@hotmail.com> wrote in message
news:2i*****************@text.news.blueyonder.co.u k...

Hi,

I've been using PHP for a while now and I'm beginning to migrate to
PHP5. So far, I'm impressed.

I need to develop an admin application to enable users to update a
website that is built on a MySQL database. In most cases, I'd simply
develop an application to manipulate the database but as the website is
updated fairly infrequently, I am considering a number of further
options instead to alleviate server load.

I am considering retaining the MySQL database for structure and building
scripts to generate XML from the data that can be processed and
transformed server-side (XSLT). Another option is SQLite. And yet
another approach is to generate static HTML pages from the database when
it needs to be updated or published.

Based on knowledge and past experience, what would everyone recommend? I
would welcome any feedback to help me in my decisions.

Thanks in advance.

Matt

We've gone though something like this. Honestly, I like the simplicity of
using mySQL and generating the pages dynamically. We did end up caching the
output for a few hours at a time some custom built code, was really simple
actually.

HTH,
Joe

[Matthew Bates]

Joe Webster wrote:

"Matthew Bates" <ma********@hotmail.com> wrote in message
news:2i*****************@text.news.blueyonder.co.u k...

Hi,

I've been using PHP for a while now and I'm beginning to migrate to
PHP5. So far, I'm impressed.

I need to develop an admin application to enable users to update a
website that is built on a MySQL database. In most cases, I'd simply
develop an application to manipulate the database but as the website is
updated fairly infrequently, I am considering a number of further
options instead to alleviate server load.

I am considering retaining the MySQL database for structure and building
scripts to generate XML from the data that can be processed and
transformed server-side (XSLT). Another option is SQLite. And yet
another approach is to generate static HTML pages from the database when
it needs to be updated or published.

Based on knowledge and past experience, what would everyone recommend? I
would welcome any feedback to help me in my decisions.

Thanks in advance.

Matt

We've gone though something like this. Honestly, I like the simplicity of
using mySQL and generating the pages dynamically. We did end up caching the
output for a few hours at a time some custom built code, was really simple
actually.

HTH,
Joe

I agree with you. MySQL and PHP work so well together that making
dynamic pages is so simple. However it seems inefficient to keep
accessing a database when content does not change that often. The
caching seems a good idea and as you said it is easily implemented.

Does anyone have any thoughts on an XML approach? Is an XSL
transformation quicker/more efficient than a MySQL query that generates
the same page?

I'm willing to consider any ideas :).

Thanks,
Matt

[R. Rajesh Jeba Anbiah]

Q: How should I handle database load?
Q: What caching mechanism are you suggesting?

A: Caching can be classified in to:
1. Browser cache
2. File based cache. (eg) <http://www.jpcache.com/>
3. Database query cache (eg)
<http://dev.mysql.com/doc/mysql/en/query-cache.html>
4. Shared memory cache (eg) <http://eaccelerator.net/>
5. Distributed memory object cache (eg)
<http://www.danga.com/memcached/>

Caveats:
(2) may significantly reduce database load. But, it has lot of
setbacks:
1. may create lot of cache files
2. if file locking (as in jpcache) is used that will slow down
3. swap file caching instead of locking may consume lot of space

Refer:
http://jeremy.zawodny.com/mysql/scal.../slide0119.htm
++++
@todo Explain browser cache, info on Zend Accelerator, APC, etc