Storing Data Efficiently

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Storing Data Efficiently

Post by kendall »

Hi guys,

I'm building a data driven website using PHP/ MySQL. I'm storing data in both xml files and database tables with xml file holding more "content" and "image" information while the database holds more "statistical" info however both would have similar information with slight differences.

By doing this I am keeping the queries to the database for mostly search/ match statements as I would mainly retrieve most of the information i need from the xml file.

My theory for doing this means having to retreive less data and save complex query statements for more search/ and match criterias thus taking significant load of execution counts and data loading...

Is this however a "good" practice or approach given that the website is expected to incurr alot of heavy information and demanding database querying..?
Last edited by kendall on Wed Apr 23, 2008 6:58 pm, edited 1 time in total.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Storaing Data Efficiently

Post by Kieran Huggins »

simple database queries are pretty cheap, and MySQL is HIGHLY optimized compared to XML parsers. I'd be more inclined to put everything in the DB and implement a static caching model.

Also, I hate XML. Just saying.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: Storing Data Efficiently

Post by kendall »

Kieran Huggins wrote:Also, I hate XML. Just saying.
hahaha....

the reason i'm using xml is to implement ajax(woohooo SPRY). it is also portable so i'm hoping that the site information can be easily "blogged" to a feed.

but what is a
Kieran Huggins wrote:static caching model
? :?:
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Storing Data Efficiently

Post by Kieran Huggins »

the general idea is that you generate static partials of things like rendered posts, so that you can just include them instead of having to hit the database each time. You only need to update (in caching terms "invalidate") the cached view when (or if) the post is edited. It's less of a processing hit per request.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: Storing Data Efficiently

Post by kendall »

Kieran Huggins wrote:the general idea is that you generate static partials of things like rendered posts, so that you can just include them instead of having to hit the database each time. You only need to update (in caching terms "invalidate") the cached view when (or if) the post is edited. It's less of a processing hit per request.
:?: 8O :? :| :dubious:
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Storing Data Efficiently

Post by Kieran Huggins »

when a post is created or saved, write the output HTML snippet to a file. Then, when you're displaying the post (during a regular web request) you can just include() that file. That way, no database call, and no XML parsing needs to be done.

If a post is deleted, you can simply delete the cache.

The same can be done for RSS feeds, you can generate the RSS XML once, then serve it directly. Every time you add a new post, you re-generate that XML file to include the last 10 posts or so.

Since most websites are "write once, read many" you'll have a very lean system.

For ways to make it even sexier, google "write-through cache" - a system that sits between the app and the database, only actually fetching data when the cached copy is either missing or invalid. Every time data is written to a W-T C it invalidates the cached copy (if there is one) and writes the data to the DB. The next request for that data will force the cache to generate a cached copy, which it will serve indefinitely instead of asking the DB for the same data again. You save both the DB call AND the processing time to build the partial xhtml on most requests.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: Storing Data Efficiently

Post by kendall »

how does this work in relation to what your or i are discussing....

http://www.databasejournal.com/features ... hp/3110171
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Storing Data Efficiently

Post by Kieran Huggins »

"query cache" is purely a mysql-based cache. The theory is that it caches common queries in memory so it can avoid the costly disk time of pulling them out every time. You'll still need to hit the DB, but it should help if you're requesting the same info all the time.

Try it and see if the performance is good enough, it's certainly easier to implement.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Storing Data Efficiently

Post by timvw »

You would have to explain what "efficiently" means to you... Anyway, in virtually every situation the use of xml is inefficient. It is slow, verbose and requires lots of memory to process... The only reason to use xml (imho) is for inter-operability with other systems, thus i would only provide import/export functionality for xml..
User avatar
dbevfat
Forum Contributor
Posts: 126
Joined: Tue Jun 28, 2005 2:47 pm
Location: Ljubljana, Slovenia

Re: Storing Data Efficiently

Post by dbevfat »

Kieran Huggins wrote:"query cache" is purely a mysql-based cache. The theory is that it caches common queries in memory so it can avoid the costly disk time of pulling them out every time. You'll still need to hit the DB, but it should help if you're requesting the same info all the time.

Try it and see if the performance is good enough, it's certainly easier to implement.
The usability of query cache is questionable. Mysql query cache stores results sets for each distinct query, so you can end up having multiple copies of the same rows, if these rows were returned from different queries. Also, the query cache is completely flushed when any data in the table changes, even if it doesn't affect the resultset for a particular query. Nevertheless, it can be usable, you just have to be aware of these facts.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: Storing Data Efficiently

Post by kendall »

timvw wrote:You would have to explain what "efficiently" means to you... Anyway, in virtually every situation the use of xml is inefficient. It is slow, verbose and requires lots of memory to process... The only reason to use xml (imho) is for inter-operability with other systems, thus i would only provide import/export functionality for xml..

oh really?....ok my scenario for storing data as xml is to be used by ajax to retrieve and display the data (trying to implement an ajax driven site). I was thinking since some of these data don't really change that often i would store it in xml files. This makes the data portable and can be distributed if needed be to other applications even outside of the website (feeds). Now the the whole parsing of xml would only happen backend. I had no intentions of letting php handle parsing of the xml. PHP would have retrieved id # from a query and the id's would have been used to retrieve the xml file. since the query would only be retrieving mostly atleast for the most 3 field columns I thought this may have been a bit easier. As retrieving the rest of the data would have been from ajaxking the appropriate xml file(s). I would have thought that 'include()'-ing files would have been more robust than having to compiling the results from a data query into xml format and outputting it.

Maybe I have over estimated my theories.... :? :| :(
Post Reply