Storing Data Efficiently
Moderator: General Moderators
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Storing Data Efficiently
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..?
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: Storaing Data Efficiently
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.
Also, I hate XML. Just saying.
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Storing Data Efficiently
hahaha....Kieran Huggins wrote:Also, I hate XML. Just saying.
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
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: Storing Data Efficiently
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.
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Storing Data Efficiently
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: Storing Data Efficiently
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.
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.
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Storing Data Efficiently
how does this work in relation to what your or i are discussing....
http://www.databasejournal.com/features ... hp/3110171
http://www.databasejournal.com/features ... hp/3110171
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: Storing Data Efficiently
"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.
Try it and see if the performance is good enough, it's certainly easier to implement.
Re: Storing Data Efficiently
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..
Re: Storing Data Efficiently
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.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.
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Storing Data Efficiently
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....