Getting all data once for future use

Question

Well this is kind of a question of how to design a website which uses less resources than normal websites. Mobile optimized as well.

Here it goes: I was about to display a specific overview of e.g. 5 posts (from e.g. a blog). Then if I'd click for example on the first post, I'd load this post in a new window. But instead of connecting to the Database again and getting this specific post with the specific id, I'd just look up that post (in PHP) in my array of 5 posts, that I've created earlier, when I fetched the website for the first time.

Would it save data to download? Because PHP works server-side as well, so that's why I'm not sure.

Ok, I'll explain again:

Method 1:

1. User connects to my website
2. 5 Posts become displayed & saved to an array (with all its data)
3. User clicks on the first Post and expects more Information about this post.
4. My program looks up the post in my array and displays it.

Method 2:

1. User connects to my website
2. 5 Posts become displayed
3. User clicks on the first Post and expects more Information about this post.
4. My program connects to MySQL again and fetches the post from the server.

Answer 1

First off, this sounds like a case of premature optimization. I would not start caching anything outside of the database until measurements prove that it's a wise thing to do. Caching takes your focus away from the core task at hand, and introduces complexity.

If you do want to keep DB results in memory, just using an array allocated in a PHP-processed HTTP request will not be sufficient. Once the page is processed, memory allocated at that scope is no longer available.

You could certainly put the results in SESSION scope. The advantage of saving some DB results in the SESSION is that you avoid DB round trips. Disadvantages include the increased complexity to program the solution, use of memory in the web server for data that may never be accessed, and increased initial load in the DB to retrieve the extra pages that may or may not every be requested by the user.

If DB performance, after measurement, really is causing you to miss your performance objectives you can use a well-proven caching system such as memcached to keep frequently accessed data in the web server's (or dedicated cache server's) memory.

Final note: You say

PHP works server-side as well

That's not accurate. PHP works server-side only.

Answer 2

Have you think in saving the posts in divs, and only make it visible when the user click somewhere? Here how to do that.

Answer 3

Put some sort of cache between your code and the database.

So your code will look like

if(isPostInCache()) {
    loadPostFromCache();
} else {
    loadPostFromDatabase();
}

Go for some caching system, the web is full of them. You can use memcached or a static caching you can made by yourself (i.e. save post in txt files on the server)

Answer 4

To me, this is a little more inefficient than making a 2nd call to the database and here is why.

The first query should only be pulling the fields you want like: title, author, date. The content of the post maybe a heavy query, so I'd exclude that (you can pull a teaser if you'd like).

Then if the user wants the details of the post, i would then query for the content with an indexed key column.

That way you're not pulling content for 5 posts that may never been seen.

Answer 5

If your PHP code is constantly re-connecting to the database you've configured it wrong and aren't using connection pooling properly. The execution time of a query should be a few milliseconds at most if you've got your stack properly tuned. Do not cache unless you absolutely have to.

What you're advocating here is side-stepping a serious problem. Database queries should be effortless provided your database is properly configured. Fix that issue and you won't need to go down the caching road.

Saving data from one request to the other is a broken design and if not done perfectly could lead to embarrassing data bleed situations where one user is seeing content intended for another. This is why caching is an option usually pursued after all other avenues have been exhausted.