MySQL? XML? XML and MySQL? SQLite?!

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

re: MySQL? XML? XML and MySQL? SQLite?!

Matthew Bates wrote:
[color=blue]
> 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.
>[/color]

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.

re: MySQL? XML? XML and MySQL? SQLite?!

>>I need to develop an admin application to enable users to update a[color=blue][color=green]
>>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.
>>[/color]
>
> 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.[/color]

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

re: MySQL? XML? XML and MySQL? SQLite?!

"Matthew Bates" <mattybates@hotmail.com> wrote in message
news:tGz_d.114$Ab.50@text.news.blueyonder.co.uk...[color=blue][color=green][color=darkred]
> >>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.
> >>[/color]
> >
> > 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[/color][/color]
it[color=blue][color=green]
> > doesn't happen often you want to do a lot of work and add a lot of
> > complication to it.[/color]
>
> 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.
>[/color]

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

re: MySQL? XML? XML and MySQL? SQLite?!

"Matthew Bates" <mattybates@hotmail.com> wrote in message
news:2il_d.6032$QN1.5239@text.news.blueyonder.co.u k...[color=blue]
> 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[/color]

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

re: MySQL? XML? XML and MySQL? SQLite?!

Joe Webster wrote:[color=blue]
> "Matthew Bates" <mattybates@hotmail.com> wrote in message
> news:2il_d.6032$QN1.5239@text.news.blueyonder.co.u k...
>[color=green]
>>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[/color]
>
>
> 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[/color]

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

re: MySQL? XML? XML and MySQL? SQLite?!

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