CODEWITHSUNDEEP
phpsqlfile-io
imin
imin

Reputation: 4578

which one consume less resources? opening text file or make an sql query,both a thousand times?

I've a php website which displays recipes www.trymasak.my, to be exact. The recipes being displayed at the index page is updated about once a day. To get the latest recipes, I just use a mysql query which is something like "select recipe_name, page_views, image from table order by last_updated". So if I got 10000 visitors a day, obviously the query would be made 10000 times a day. A friend told me a better way (in terms of reducing server load) is when I update the recipes, I just put in the latest recipe details (names,images etc) into a text file, and make my page instead of querying a same query for 10,000 times, just get the data from the text file. Is his suggestion really better? If yes, which is the best php command should I use to open, read and close the text file?

thanks

Upvotes: 3

Views: 311

Answers (6)

Alfred
Alfred

Reputation: 61771

Benchmark

To know the truth about something you should really benchmark it. "Simple is Hard" from Rasmus Ledorf(Author of PHP) are really interesting video/slides(my opinion ;)) which explain how to benchmark your website. It will teach you to tackle the low hanging fruit of your website instead of wasting your time doing premature optimizations.

Donald Knuth made the following two statements on optimization: "We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil"

"In established engineering disciplines a 12 % improvement, easily obtained, is never considered marginal and I believe the same viewpoint should prevail in software engineering"5

In a nutshell you will run benchmarks using tools like Siege, ab, httperf, etc. I would really like to advice you to watch this video if you aren't familiar with this topic, because I found it a really interesting watch/read.

Speed

If speed as your concern you should have at least consider:

  1. Using a bytecode cache => APC. Precompiling your PHP will really speed up your website for at least these two big reasons:

    Most PHP accelerators work by caching the compiled bytecode of PHP scripts to avoid the overhead of parsing and compiling source code on each request (some or all of which may never even be executed). To further improve performance, the cached code is stored in shared memory and directly executed from there, minimizing the amount of slow disk reads and memory copying at runtime.

    PHP accelerators can substantially increase the speed of PHP applications. Improvements of web page generation throughput by factors of 2 to 7 have been observed. 50 times faster for compute intensive analysis programs.

  2. Us an in-memory database to store your queries => Redis or Memcached. There is a very very big mismatch between memory and the disc(IO).

    Thus, we observe that the main memory is about 10 times slower and I/O units 1000 times slower than the processor.

    The analogy part is also interesting read(can't copy from google books :)).

Upvotes: 3

Capsule
Capsule

Reputation: 6159

Opening ONE file is faster that doing a mysql connect + query. BUT, if your website already needs a mysql connect to retreive some other informations, you probably want to stick with your query because the longuest part is the connection and your query is very light.

On the other hand, opening 10 files is longer than query 10 records from a database, because you only open one mysql connection.

In any case, you have to consider how long is your query and if caching it in a text file will have more pros than cons.

Upvotes: 0

swedishhousemafia
swedishhousemafia

Reputation: 129

If you try it, store the recipes in a folder structure like this:

/recipes/X/Y/Z/id.txt where X, Y and Z is a random integer, from 1 to 25

example: /recipes/3/12/22/12345.txt

This is because the filesystem is just another database. And it has a lot more hidden meta data updates to deal with.

I think MySQL will be faster, and certainly more manageable, since you'd have to backup the MySQL db anyway.

Upvotes: 0

miku
miku

Reputation: 188024

Databases are more flexible, secure and scalable in the long run. 10000 queries per day isn't really that much for modern RDBMS either. Go (or stay) database.

Optimize on the caching side of things, the HTTP specification has an own section on that:

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

The typical solution is to cache in memory. Either the query result or the whole page.

Upvotes: 3

binaryLV
binaryLV

Reputation: 9122

Reading files:

Upvotes: 0

Related Questions