Caching help

[karentutor1]

Hi

On my front page (index.php) I do 2 queries that can pull quite a bit of data.

I am concerned that it is too resource intensive. What I would like my system to do is to update the page itself say every 5 mins and display that data - rather than rerunning the two queries every time someone lands on the page.

I believe hat this is called “caching” but I am unsure exactly what it is and how to do it.

If someone could name my problem more accurately and suggest an approach with some tools - I should be able to figure out the particulars.

I am right now a little unsure what and how to do this - or even if I should.
Thanks!
Karen

[r937]

yes, query caching is the appropriate terminology

it very much depends on which software you want to use or have access to

my former web site¹ was database driven, with ColdFusion as the front-end language, which allowed query caching right in the individual query syntax –

    <CFPARAM NAME="request.whichentryid" DEFAULT="10">
    <CFTRY>
    <CFQUERY NAME="sqlatequery" 
             DATASOURCE="#request.mysqlDSN#"
             USERNAME="#request.mysqlusername#"
             PASSWORD="#request.mysqlpassword#"
       CACHEDWITHIN="#CreateTimeSpan(0,1,0,0)#" 
             BLOCKFACTOR="100" >
    select name 
         , url  
         , date_format(added,"%e %M %Y") as dateadded
      from links  
     where privacy = 0
       and entryid =   #request.whichentryid# 
    order
        by added desc
    </CFQUERY>
    <CFCATCH TYPE="Database">
      <CFSET request.errormsg = 
           ListAppend(request.errormsg
             ,"Database error D937F04C; please notify administrator. ")>
    </CFCATCH>
    </CFTRY>
    <CFIF Len(request.errormsg)>
    <CFELSE>
      <!--- this query is allowed to return 0 rows --->
    </CFIF>

your mileage with php may vary

¹ it’s now static html only

[karentutor1]

Thanks ok so it is called query caching.

I am using vanilla php and reverse proxy nginx in front of Apache… Any other pointers?

Deepest thx!

[rpkamp]

Query caching is something that is done by MySQL itself, you don’t need to set that up. What you might need to set up is how big those caches are, but if it’s only two queries I’m pretty sure you’ll be fine running the defaults.

Have you done some actual tests that show the queries are slow, or is it just a feeling you have? I would always make sure first that it’s actually a problem before trying to solve it. Premature optimisation is the root of all evil.

[karentutor1]

Haha lol - just a “feeling”.

I will hold the premature optimization. You are right it quickly gets crazy when you start looking into all the things that “can” be done. I can probably just start with nginx, single MariaDB and one server!!

Hmm… cool perspective!