我应该如何在考虑性能的情况下将XML，JSON与MYSQL DB混合使用

Question

I'm developing a php site where users select data resources from a variety of categories. The resources come from varied sources, some RSS, some XML, some JSON, some hosted internally, some hosted externally. The user has the ability to edit which resources they will see and that information will be stored both in the data base as well as cookies, sessions and caches to lower the load on the server when the user is not actively selecting resources. Some of the tables in the database will be largish(for me anyhow) ranging from 20,000-50,000,000 entries. Other tables will be quite small ranging from 51-200 entries. These smaller tables are mainly name tables things like state names, category names and other similar things.

Because this is a relatively large project for me I want to focus on optimizing server resources and I'm asking myself whether hosting some of these small tables as xml(json or includes work just as well) and integrating them via ajax might be a more efficient usage of resources. Additionally if anyone knows what the potential performance gain or penalty for using resources that way might be and what the best practices for mixing data like that are. I should note that on the front end the site will be pretty lean so I don't mind passing off work to the browser.

As an simple example

I'm going to need to store a table of US State names, Acronyms and IDs somewhere. Its a small but essential list and I really don't want to have to query for state name every time I need to use a State somewhere. Will I suffer a performance penalty if I just toss a State Table in XML and use a function to access it via ID as required, or would I be better served keeping it in my DB and running queries? Or should I just cache the results of the query somewhere and access it that way?

Answer 1

Your question as stated is really too broad for protocol here, but let me try to get you headed in the right direction...

Use XML for exchange where:

• Industry standard schemas already exist, or you must coordinate format agreement among partners and can benefit from the definitional maturity of XSD or the transformational flexibility of XSLT.
• Your data is naturally document-based, especially where mixed content is required.

Use JSON for exchange where:

• The above reasons for XML do not apply.
• Easy programmatic access to simple, light-weight data structures is helpful.

Use a database for storage where:

• ACID properties are important.

Use code-based static data for performance where:

• The data never changes and access speed is paramount.

So, your given example of a very small static look-up table, where you're concerned about performance, probably fits best in code. Do avoid premature optimization, though.