1

I am thinking about creating a json/xml/text when something is inserted to mysql database, so that php page will read this instead of mysql queries. When a data has updated in mysql database, it will update json/xml files too. Is this a good idea? It seems, this will has less mysql queries so I think this can be good.

Improve this question
5
  • 5
    You'll have a lot of overhead and consistency issues. What is the problem with MySQL queries? Commented Feb 29, 2012 at 9:06
  • i tought less queries will be better for server.I'm thinking to use this on pages which are not so active, has 1-2 updates for whole table. Commented Feb 29, 2012 at 9:07
  • depends on the frequency the site is being viewed and the size of the db. If the content of the database is big i would rather use mysql. Commented Feb 29, 2012 at 9:08
  • try xml as your database instead of mysql for some kind of servicing data ,, i mean REST ful services Commented Feb 29, 2012 at 9:13
  • The results of your queries could also be cached by the server using the dev.mysql.com/doc/refman/5.1/en/query-cache.html Commented Feb 29, 2012 at 9:14

4 Answers 4

Reset to default
7

it seems, this will has less mysql queries so i think this can be good.

It depends. Probably not.

What you plan to do is a form of caching. If the queries used on a page are really, really complex and take a long time when benchmarking, it is sometimes a good thing to store the result in a cached file, and updating that if data changes.

But having some simple SELECT queries running on every request is nothing to worry about performance-wise. If you're not seeing any actual performance issues, you're probably best off sticking with SQL queries. A properly indexed database is really, really fast.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I want to use this for pages that doesnt updated so much, like archive pages or old news. But yes, has simple queries.
@Malix then caching probably won't make sense.
6

What's your goal? Gain more performance?

1st rule for performance optimization: Know your bottleneck! Don't optimize a black box. Do benchmarks before and after each optimization.

I doubt, that your database is your current bottleneck. Even it is, there are better ways to improve the performance, i.e. avoid statements, instead of storing the results in a json/text file.

  1. Use MySQL Query cache

    MySQL is really good in caching results. You shouldn't take care of caching results yourself. Important here: enough RAM. Checkout the MySQL docs for more information: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

  2. Implement more cache layer

    To gain more performance you could implement a second cache layer before the MySQL database to avoid even the call to the database. You could store the initial results (at the first call you need to get the result from the DB, of course) in a non persistent, awesome fast way using memcached or other NoSQL solutions (Redis, MongoDB to name a few).

  3. Know your bottleneck

    As I said before, the most important thing in performance optimization is to know where your bottleneck is, and how much you won with a optimization. At least it's a very good feeling to stat: Hey,... after we did XYZ the system is 6000% faster. :)

Maybe your database isn't the thing which needs to be speeded up right now. Perhaps installing APC will speed up your application. :)

But coming back to you question: If you really want to store those results in JSON/text file, you should store the files in a tmpfs / RAM filesystem - so that they can be written and read very fast.

Improve this answer

Comments

5

This really depends I suppose. If you are writing a page to be loaded with AJAX, and you have settings or configurations that are very rarely changed but needed to be loaded each time, then go ahead and write it to a json file.

If the content you are loading is dynamic or you are loading it via server side scripts, then MySQL will be a must.

Beware: If you start writing settings or data to a json file, you may encounter a million issues with security and permissions.

Improve this answer

1 Comment

I was mid way through writing a very simular answer, but yours pretty much covers it. The security implications of storing your sensitive information in an open json file would be dire. An attacker with any knowledge would be able to download the file and read all the information in it. I would highly recommend you use the MySQL table due to security, ease of use, and future proofing
2

Depends on your application size. If is about few things to interact with database. you can do with JSON storing.

But If database size is large and want to use RDBMS concepts then it will be difficult to manage.

I would like you to go through this article:

MySQL-vs-JSON-file-data-storing

Improve this answer

1 Comment

@Malixxl don't let this terrible article fool you. It has nothing to do with real life.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.