store in array or use multiple db queries

Question

Within a php/mysql system we have a number of configuration values (approx 200) - these are mostly booleans or ints and store things such as the number of results per page and whether pages are 2 or 3 columns. This is all stored in a single mysql table and we use a single function to return these values as they are requested, on certain pages loads there can probably be up to around 100 requests to this config table. With the number of sites using this system this means potentially thousands of requests each second to retrieve these values. The question is whether this method makes sense or whether it would be preferable to perform a single request per page and store all the configs in an array and retrieve from here each time instead.

Answer 1

If you enable MySQL query cache, the query that selects your values will be cached in memory, and MySQL will give an instant answer from memory unless the query or data in the underlying tables are changed.

This is excellent both for performance and for manageability.

The query results may be reused between the sessions: that means, if you have 1000 sessions, you don't need to keep 1000 copies of your data.

Answer 2

You might want to consider using memcache for this. I think it would be faster than multiple DB queries (even with query caching on), and you won't need a database connection to get them.

You might want to consider just loading them from a flat file into memory, this affords you the opportunity to version control your config values.

Answer 3

Use a cache such as memcache, APC, or any other. Load the settings once, cache it, and share it through your sessions with a singleton object.

Even if the query cache is saved, it's a waste of time and resources to query the database over and over. Rather, on any request that modifies the values, invalidate the cache that is in memory so it is reloaded immediately the next time someone requests a value from it.

Answer 4

I would defintely recommend memcache for this. We have a similar setup and it has noticably brought resource usage down on that server.