I am trying to write a strategy game with PHP, and basically I have a 1d based tile map (grass, dirt, sand, castles, etc.) stored in a database. Now where is 40k records, from x1y1 to x200y200 and I might increase it in future. Where is few column in this map table, it's id,x,y,occupied,terrain,city_type My question is, what should be a working caching strategy for something like this? Should game cache it by request, i.e. when player requests x189y15 with radius 5, it will cache the resulting array of tiles and when display it to anyone who requests map with the same x,y and display radius. But what is not efficient because of radius. I thought of storing the whole map in array, but I run into two problems, firstly, how would game select data from array within needed radius? Second, if it can is there a point then? Wouldn't this array be as slow or slower than db access? What is a (best) perfomant caching strategy for this case? Does this need caching at all?
PHP, without the use of dedicated extensions, is bad for this task.
The main problem is that in PHP the execution of requests is compartmentalized. It serves each request in an isolated thread, and clears all resources after the request has finished. With the exception of session variables, database and permanent storage, there is not a real cache solution in PHP (at least not by default).
Note: the only way to spawn another thread (without extensions) is to make another request.
This is good for IT admins because it means no leaks and no deadlocks. With the appropriate configuration, it means no huge CPU loads running in the background indefinitely. In addition, the resources needed for the server are directly proportional to the number of requests, so it is possible to find a maximum number of requests that will not deteriorate the performance of the server. Easy IT administration means cheap IT administration, which means cheap servers (and that is without saying that PHP is free).
If your map does not change, you can have the client cache the information. Otherwise, look into cache extensions of PHP.
There are two types of caches to look into: for code such as OPCache, and for data such as Memcache. If your data does not change then both may work, otherwise you want a data cache.
When it comes to handle large maps, video games will often load this kind of information to RAM and keep it there (but you cannot really do that on PHP). The load time can be optimized using files that contain distinct chunks of the data that can be loaded depending on what region the player is (the database can be considered to be doing a similar optimization).
Since no information is kept from request to request in PHP... if you put all the data on arrays on a PHP files, the server will have to load it into RAM every time one of those PHP file is loaded. For a small arrays this works better. If the data is too big, you are better off loading it from the file system (fopen et.al.), database or a dedicated cache extension (which can keep data in RAM between requests).
Note: the file system will have its own cache solution depending on the operating system.
Code cache will compile the PHP files and keep them in bytecode or binary format between requests (resulting in better response time). If you do this, remember that you have to edit the PHP files to update the arrays, and that you have to invalidate the cache when you do that in order to allow the server to recompile the files... if this happens too often, the benefits of the code cache are minimal.
Therefore, if your data changes too often, you want a data cache. You can push and pull data to the data cache using a key, resourcing to database if the data is not available there. Remember that you pull all the data under that key, there will be an ideal chunk size to store per key (it may be a single tile, but definitively not the whole map), testing advised.