99 posts
  • Has been part of the Envato Community for over 5 years
  • Has referred 10+ members
  • Has sold $1,000+ on Envato Market
  • Located in Belgium
+2 more
krike says

I purchased sitebase cache class and I was starting to cache most of the query results on my site untill I came across this Q&A on stackoverflow: http://stackoverflow.com/questions/5441344/cache-the-database-result-in-txt-file-and-how-to-output-it/5441384#5441384

Apparently it takes longer to read from the disk then to query the database. So I’m a bit confused because this is the first time I hear this. I always thought cache query results was much faster then to query the db. Can someone explain if I should cache the results of my query or just leave the query like it is now?

3435 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $500,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark Volunteer moderator says

I’m not familiar with Sitebase’s plugin, but DB query caches are usually still stored in the DB. It’s not the DB access that is slow – it’s the execution of complex queries that slows things down. Query results get cached and stored in the DB in a way that can be retrieved very quickly and efficiently.

Then again, DB tables are still stored on the disk. The DB is faster because the data is cached by the databases’ internal caching mechanism and stored in memory. The problem the guy on StackOverflow was bringing up is that your “cache” was written to disk rather than caching in memory. I doubt this is how Sitebase’s script is implemented.

499 posts
  • Has referred 1+ members
  • Has sold $1,000+ on Envato Market
  • Has collected 10+ items on Envato Market
  • Located in Canada
+2 more
TutelageSystems says

It really depends on what you want to cache and if it is worth it, take this example below.

You are running a PHP / MySQL / Oracle application. You need to pull in data from Oracle and it’s stored procedures (they pull data from hundreds of tables into a single table for the report).

The normal way it would be programed is when a user clicks on the report to view, you call the Oracle Stored Procedure which returns the cursor (results) to PHP . Lets say this process takes about 5 minutes. The user cannot choose any options to keep things simple.

You have about 10 people that use that application. This is when you want to start caching results. So when the next person comes in to run that report, your program will see that a cached version exists for those options and that day, and it would grabbed the cached data and not run the Oracle Stored Procedure for 5 minutes (unless it is a new day).

I have no used Sitebase’s Cache Class, but if it is anything like the PEAR Library then it again depends on what you want to cache and how.

There is no reason you need to cache something like; grabbing the names out of a table, or querying a birthday.

55 posts
  • Has been part of the Envato Community for over 4 years
  • Has sold $100+ on Envato Market
  • Has collected 10+ items on Envato Market
  • Sells items exclusively on Envato Market
cuteprince says

Yes! Reading data from disk requires more execution time as compared to DB and thats the reason why DBs are used. I’ll Recommend you to leave it as it is.

72 posts
  • Has been part of the Envato Community for over 3 years
  • Has referred 100+ members
  • Has sold $125,000+ on Envato Market
  • Has collected 1+ items on Envato Market
+4 more
yougapi says

Depends on what you want to cache. I will give you an example based on my experience.

On my of my web app, a user can attach his Twitter accounts (1 or several), his Facebook account, he has a picture, and personal settings.

When a user login I need to request all these data, than are stored in several tables.

Certain data needs to be loaded on all pages (ex: the user picture).

So what to do?

You can use sessions. But when you have more than 10 or 20 data to save, maybe session is not a good option. Because the sessions go and come back each time the server is called (even for AJAX requests…).

The other option is to load all the needed data from MySQL each time (so for certain data on each page). That could be optimized to avoid requesting MySQL too much.

For this case it’s worthing I believe using a cache system. In my case when a user login, I save all the user’s data in a SQLite database in one single row. That acts like a caching system.

So when I need to request all that user’s data, I just request that SQLite database, but I do it with one SQL select (instead of having to request several tables from MySQL…). So in this logic I think it’s good to have a caching.

But if it’s making a regular select or cases that don’t repeat etc, it’s not worthing adding a layer that will make the app a little more complex, without bringing a lot of benefits.

Even though in general accessing a file could be slower than requesting the database, if the queries you requests from MySQL are complex enough, or takes time, it can be better to have a file caching system. Depends on the cases involved…

3055 posts
  • Located in Australia
  • Helps us moderate the forums
  • Has been part of the Envato Community for over 5 years
  • Helped several times protecting Envato Market against copyright violations
+16 more
dtbaker Volunteer moderator says

Wrap some time code around your script to test if caching vs db query is faster.

eg:

$start_time = microtime(true);
// run your code here....
echo 'This script took '.(microtime(true) - $start_time) .' to run';

generally caching database queries is only useful if the queries take a long time to run or are very complicated (joins etc..)

generally you cache external requests (eg: a twitter feed, rss feed, results from a curl request, etc..) for a period of time so your script runs faster.

if a complicated php page takes 2+ seconds to render then it might be useful to cache the entire html output of the page for a period of time.


You can use sessions. But when you have more than 10 or 20 data to save, maybe session is not a good option. Because the sessions go and come back each time the server is called (even for AJAX requests…).

with sessions only the “session id” is passed backwards and forwards. no “data” is passed backwards and forwards each time. so you can save as much information in the session as you like and it will not affect the http request/response size in any way.

99 posts
  • Has been part of the Envato Community for over 5 years
  • Has referred 10+ members
  • Has sold $1,000+ on Envato Market
  • Located in Belgium
+2 more
krike says

aaah oke, this clear things up, thank you for the tips.


Wrap some time code around your script to test if caching vs db query is faster.

eg:

$start_time = microtime(true);
// run your code here....
echo 'This script took '.(microtime(true) - $start_time) .' to run';

generally caching database queries is only useful if the queries take a long time to run or are very complicated (joins etc..)

generally you cache external requests (eg: a twitter feed, rss feed, results from a curl request, etc..) for a period of time so your script runs faster.

if a complicated php page takes 2+ seconds to render then it might be useful to cache the entire html output of the page for a period of time.

thanks, I’ll give this a try

by
by
by
by
by
by