PHP - Is it good practice to cache MYSQL queries in a txt file?

Question

I'm building an online shop & trying to improve performance by minimising MYSQL queries.

Is it good practice to cache the mysql queries via a txt file and then fetch that instead of the query? This is what I'm doing"

1. A php class takes the sql query as a string
2. does an md5 of it
3. if this is the first time it's run
4. then perform the query on the database
5. get the results in an array
6. serialize the array and store it as md5_Of_Query.txt
7. return either unserialize(file_get_contents(md5_of_Query.txt)) or $results of actual query, depending on whether or not the cache exists and is valid.
8. The class also checks the filemtime() of the txt file and if its greater than say, one hour old, then re-perform the query and refresh the cache.

Is this more efficient than doing sql queries every time? Any security issues I'm missing?

Viviparous

Answer 1

If you're just starting the application, memcache is a much faster way to go than using text files.

http://memcached.org/

Text files will do the job, and the steps you've outlined make sense, but memcache will be faster and handle a lot of the heavy lifting for you.

Honky

Thanks a lot, Got memcache installed on my WAMP server, will build using this cache now. – Viviparous

On a single WAMP it cannot make difference, text file access on Windows is already cached in memory. Memcached adds overhead and steals memory the web server and the DB would have more use for. Memcached is meant for much much much larger scenarios. With large server farms and pools of caching servers answering queries and subqueries. In that scenario memcached (that interrogates memory trough a very textual protocol over a network socket) can squirt the best of performance out of the rich redundance of resources. Single machines can get no benefit out of it. – Supervise

Excellent point, however It is a good idea to plan for expansion at the beginning of an application's development life cycle. I don't believe that the application will be hosted on a WAMP server, I assume that is only for development and testing. – Honky

& @Supervise - Yes, the application won't be hosted on a WAMP server :). It'll be on a VPS with 512 - 1GB memory. – Viviparous

Answer 2

If you do a benchmark, the costs of doing creating a unique hash and performing IO to disk will be greater than simply fetching from the MySQL server.

IMHO, don't bother going to the extent. Good thoughts, but MySQL already has internal caching and performance tweak.

Focus on building your application, as "premature optimization is the root of all evil".

Canossa

Thanks. You've save me a lot of trial and error with your advice. – Viviparous

Answer 3

Your method looks a bit like shifting the problem from one corner into the other.

Introducing a cache is not improving Mysql performance. Better look which queries are actually slow and then optimize the queries.

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Banderillero

Answer 4

If you want to achieve caching with an eye towards future scalability, I would recommend setting up RESTful services that run the queries on the database, and then using the HTTP caching features of your web server to cache the results. The steps would look like:

1. The original page needs to run a query
2. It generates an http GET request to the service URL passing the parameters as query parameters in the URL
3. A php script located at the URL accepts the parameters for the query, validates them, and adds them to a MySql query
4. The script runs the query on the database
5. The script serializes the result and sets it as output
6. The web server caches the response for the request and returns it for future requests with the same URL
7. The original page uses the serialized results from the service to generate the HTML

You can read more about caching PHP with Apache here. What you are doing now is close to this, but your application will be able to scale better with the service-based approach.

Gasolier

Answer 5

Just wanted to weigh in my two cents, what serialworm and thephpdeveloper said share the fact that memory/ram is much faster than any disk IO bound operation you come up with. Throw as much ram as you can to mysql and you won't need to deal with cache management unless you really need to upgrade to a cluster, and that needs other considerations. Memcache gives you more control over cache management and consequently you need to do more codding.

I would start by building the app then stress test it and optimize queries, and/or add cache management as needed.

Riflery

Answer 6

Two things 2 look at , benchmarking and profiling. About the only way you can compare meaningfully atything is by using the 2 metrics of these diciplines together, using your currently used mysql config, php.ini, httpd.conf, .htaccess, mod rewrite stuff and many other stuff would be benchmarking and profiling the acting technologies doing the task.

Consumptive

Answer 7

It's nonsensical, you should cache results.

The query assembly time should be pretty negligible. (If it isn't, you're not using SQL as it's supposed to, generating, instead, streams of stupid selects where a smart join would have solved)

Loading from disk the query obviously is prone to slow things down.
(The OS could be caching disk IO, though, and making it hard to spot)

What really should take time anyway, is getting the results out of the DB and then style them back into a web page trough a template. At your place I would cache to disk the styled results of a given query, and when asked, if the cache is not much old, I would directly readfile() them.

Supervise

Answer 8

Have you configured MySQL's query cache?

Peirsen