Simple query caching

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
User avatar
Toxyy
Registered User
Posts: 594
Joined: Mon Oct 24, 2016 3:22 pm
Location: Namek
Contact:

Simple query caching

Post by Toxyy » Wed Oct 24, 2018 6:22 am

I'm not sure where to begin with using caching, but I have a query that I'd like to cache. It's by far the most used query I have.

It's called here: https://github.com/toxyy/anonymousposts ... r.php#L473

This is the query in question: https://github.com/toxyy/anonymousposts ... r.php#L120

It runs on the forum index, viewforum, and anywhere there is a topic rowset.

How would I cache this optimally?
I am a web developer/administrator, specializing in forums. If you have work you need done or are too lazy to do, pm me!

My extensions:
[3.2][BETA] Anonymous Posts || [3.2][RC] Show User Activity

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1974
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: Simple query caching

Post by kinerity » Wed Oct 24, 2018 12:10 pm

It all depends on how long you want to cache it for.

Code: Select all

$result = $this->db->sql_query($is_anonymous_query, 300); // Cache for 5 minutes
If you ever need to clear the cache before the time runs out, all you need is the table name.

Code: Select all

$this->cache->destroy('sql', POSTS_TABLE);
I'm not sure, but destroying the POSTS_TABLE cache may have unintended consequences. I haven't dug too much into this.
Kailey Truscott - Community Team

User avatar
Toxyy
Registered User
Posts: 594
Joined: Mon Oct 24, 2016 3:22 pm
Location: Namek
Contact:

Re: Simple query caching

Post by Toxyy » Wed Oct 24, 2018 8:30 pm

kinerity wrote:
Wed Oct 24, 2018 12:10 pm
It all depends on how long you want to cache it for.
And that's all I have to do?

As long as the query to get the post row lasts, or maybe not... What if the top topic of the forum gets updated but the post list is the same? Will I receive improper information? I'm not looking at my code and I can't remember if the post lists are the post ids or the topic ids. Assuming they're topic id's I can foresee this problem.
I am a web developer/administrator, specializing in forums. If you have work you need done or are too lazy to do, pm me!

My extensions:
[3.2][BETA] Anonymous Posts || [3.2][RC] Show User Activity

User avatar
GanstaZ
Jr. Extension Validator
Posts: 642
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: Simple query caching

Post by GanstaZ » Wed Oct 24, 2018 8:59 pm

Best thing to do is to test. But as i understand it and have experienced, if one cached query gets updated (cache is rebuilt) and second one has old cache, you will not see what you want or you may even see something that you don't want, until it refreshes itself, is destroyed or purged.
"When answer lies in the question,.. question becomes redundant!"

User avatar
JoshyPHP
Code Contributor
Posts: 972
Joined: Mon Jul 11, 2011 12:28 am

Re: Simple query caching

Post by JoshyPHP » Wed Oct 24, 2018 11:26 pm

Toxyy wrote:
Wed Oct 24, 2018 6:22 am
How would I cache this optimally?
You don't. If you want to process this optimally, you don't even run a query. Especially not a query inside of a loop, which should definitely be replaced by a join.

You can modify the viewtopic query via the core.viewtopic_get_post_data event and the viewforum query via core.viewforum_get_topic_ids_data. Usernames are available in the same rows, or via a separate event. Worst case scenario, if you absolutely have to query them separately you can do that with a single uncached query. Keep in mind that search engines will crawl thousands of page over a short period; You don't want to bloat your cache with thousands of objects.
I wrote the thing that does BBCodes in 3.2.

User avatar
Toxyy
Registered User
Posts: 594
Joined: Mon Oct 24, 2016 3:22 pm
Location: Namek
Contact:

Re: Simple query caching

Post by Toxyy » Thu Oct 25, 2018 2:07 am

JoshyPHP wrote:
Wed Oct 24, 2018 11:26 pm
Toxyy wrote:
Wed Oct 24, 2018 6:22 am
How would I cache this optimally?
You don't. If you want to process this optimally, you don't even run a query. Especially not a query inside of a loop, which should definitely be replaced by a join.
That second query is only called during posting to get the usernames of all the top quotes, but noted. I'll look into changing the existing query instead of calling this one.
I am a web developer/administrator, specializing in forums. If you have work you need done or are too lazy to do, pm me!

My extensions:
[3.2][BETA] Anonymous Posts || [3.2][RC] Show User Activity

Post Reply

Return to “Extension Writers Discussion”

Who is online

Users browsing this forum: No registered users and 7 guests