CODEWITHSUNDEEP
phpmysqljsondatabase
Jaypee
Jaypee

Reputation: 1280

JSON Flat file vs DB querying

I'm working on a site that has a store locator built in.

Since I have similar sites developed in the past, I have experienced some troubles when I had search peaks hitting the database (mySQL) hard. All these past location search engines were querying the database to get the results.

Now I have taken a different approach, but since I'm not 100% sure, I thought that asking this great community could make me feel more secure about this direction or stick to what I did before.

So for this new search, instead of hitting the database for requests, I'm serving the search with a JSON file that regenerates (querying the database) only when something is updated, created or deleted on the locations list.

Maybe someone out there had to take the same decision and can share the experience with me, or maybe you just know how things really are and recommend me a certain approach.

Upvotes: 0

Views: 1640

Answers (1)

Mike Purcell
Mike Purcell

Reputation: 19979

Flat files are the poor man's db and can be even more problematic than a heavily pounded database. For example reading and writing the file still requires a lock, and will not scale, as the same file may not be accessible to all app servers.

My suggestion would be any one of the following:

  1. Benchmark your current hardware, identify bottlenecks, scale out or up accordingly.

  2. Implement a caching layer, this will save on costly queries for readonly data.

  3. Consider more high performant storage solutions such as Aerospike or Redis

  4. Implement a real full text search engine such as ElasticSearch or SOLR.

Response to comment #1:

You could accomplish the same thing without having to read/write a flat file (which must be accessible by all app servers), by caching the data. Here's just a quick N dirty rundown of how I would do it:

Zip + 10 miles:

Query database, pull store data, json_encode, cache using a key construct like 92562_10, then store in cache. Now when other users enter 92562 + 10 they will pull data from cache vs the database (or flat file).

City, State + 50 miles:

Same as above, except key construct may look like murrieta_ca_50.

But with the caching layer you get better performance, and the cache server will be available to all your app servers, which would be much easier than having to install/configure NFS to share the file on a network.

Upvotes: 1

Related Questions