Get MySQL data without stressing server

Question

I'm wondering what the best way is to prevent my server from being stressed when I want to get a lot of data using PHP.

Convert all my data to an XML sheet? Use caching?

The data comes from many different databases and different tables.

Any ideas?

Thanks in advance

Answer 1

I think caching will be the way to go , except if this data doesn't change very often :)

Answer 2

1. Before you do anything, stress it until it breaks.

2. When it breaks, profile it to identify the breaking point, and work on that.

3. Repeat from point 1 until the performance limits are acceptable.

Answer 3

Neither.

Both add significant complexity within your PHP code. Converting to XML has absolutely no added value - indeed not only is it an additional cost encoding in XML, you also have a cost for decoding. As for actucally storing your data in XML - this is plain ridiculous.

The MySQL DBMS provides very effective result caching. The underlying OS should provide very good I/O caching. You gain nothing by adding your own caching layer except for slower code and hotter cpus.

If you have an OLTP type database and are routinely collating large sets of data, then you might want to think about pre-consolidating it or implementing an OLAP schema within your database.

OTOH if you simply want to improve the performance of your system, then look aleswhere - particularly your data scema.

C.