How to Stop MySQL from caching SELECT statements

by

The Problem:

If your webhost uses litespeed to speed up your websites, an unintended side effect is it caches MySQL SELECT queries. In my experience it only cached them for 45-60 seconds, but a one minute delay after you add data (like a shopping cart) is not good!

 

The Test:

First of all run phpinfo:

<?php phpinfo(); ?>

And search the page for “litespeed”.

If litespeed is found, then just do the solution, because it’s an awful lot faster than setting up the test 🙂

I didn’t know the solution, so I set up a very simple page that read a number from the database, add one, and then wrote that number back to the same field.

A bit like this (readDB1 and setDB are custom functions I wrote that perform a sql SELECT and sql UPDATE so I don’t have to type that out every time. And also so I can change between PDO and mysqli when I feel like it, and a different database too if I wanted to by only rewriting a couple of lines of code. DRY is good!)

<?php
$counter1 = readDB1("aa_test_counter","aa_test_table","aa_test_id=1");
echo $counter1;
$counter2 = $counter1 + 1;
$result1 = setDB("aa_test_counter=?","aa_test_table","aa_test_id=1",$counter2);
echo " " . $counter2 . "<br />";
?>

Then I created the table, and entered the single row of data with 1 and 1.

When I ran this page on one server, every time I refreshed the page it went up. When I ran this page on the server with litespeed, it went up the first time I loaded the page, and then no matter how many times I refreshed it, the counter stayed the same until about 45 seconds had passed.

Phpmyadmin showed that the write occurred instantly. So it went from 1 to 2 and then 2 was written to the database. However, when the page refreshed, it read 1 again! if I just waited a minute and refreshed, it read 2 and then wrote 3, and so on.

 

The Solution:

How do you stop the cache?

Well, you can ask your web host to modify litespeed…

Or you can just add these two lines to the end of your .htaccess file:

CacheDisable public /
CacheDisable private /

Sometimes it might be just

CacheDisable

or

CacheDisable private

But that’s it. Took me way too many days to find that little gem.

Enjoy!

 

Leave a comment