Performance of One Query on fetch_musername Hook

[James Birkett]

I have one query being ran on the fetch_musername hook to grab the value of one custom user field. The query (off the top of my head) is:

$field = $vbulletin->db->query_first(sprintf("SELECT field16 FROM " . TABLE_PREFIX ."userfield WHERE userid = %d", $user['userid']));

I'm worried about the performance of this query and how it'll affect my server load.

Will this query be ran once and cached - or will it be ran every time the fetch_musername hook is ran?

If anyone can shed any light, that'd be great - thanks.

[kh99]

Well, of course fetch_musername() will be called at least once for each different user. The function does check the $user array that's passed as the first parameter to see if the musername field is set already, and if it is it returns right away. But of course that doesn't guarantee that it won't be called twice for a given user, if the same user array isn't passed both times.

You could make your code check for $user['field16'] being set before you do the query. And if still find your query is being done more than once per user, you could build you own cache. (You can see how many times your query is called by turning on debug mode and looking at the list of queries at the bottom of the page).

Edit:

Originally Posted by James Birkett

Will this query be ran once and cached - or will it be ran every time the fetch_musername hook is ran?

To answer this specifically, with the code you posted above it will be run each time the fetch_musername hook is run

[James Birkett]

Yeah, I've got it set so if field 16 doesn't exist then the query is ran. So far, it's ran on the visitor messages and who's online I think.

I forgot about debug mode... oops.

I'll look into that.

--------------- Added [DATE]1340797789[/DATE] at [TIME]1340797789[/TIME] ---------------

OK I've just checked this out and it's running the query once per instance of the username. If my name appears twice, it runs the query twice.

How would I go about caching this? Preferably without writing it to the DB.

[kh99]

No database needed. What I meant by caching is to keep previous answers in a global variable, because you're only caching for a single page view. I was thinking something like this:

PHP Code:

global $field16_cache;

if (!is_array($field16_cache))
{
    $field16_cache = array();
}

if (!isset($field16_cache[$user['userid']]))
{
    $row = $vbulletin->db->query_first(sprintf("SELECT field16 FROM " . TABLE_PREFIX ."userfield WHERE userid = %d", $user['userid']));
    $field16_cache[$user['userid']] = $row['field16'];
}
$field = $field16_cache[$user['userid']]; 


[James Birkett]

Why would you want $field16_cache to be an array? It only returns one value - or is that so it stores everyone's user ID?

Also, with a cache, how would I force a cache update when the value of the user field changes?

[kh99]

Maybe we're talking about different things. You only have to worry about what happens on a single load of a page, so a user's field16 isn't going to change. On another page it might be different, but the cache goes away once the page is built, so it doesn't matter.

The idea of caching like I posted above would be that if you ended up needing the value of field16 more than once for the same user on the same page, then you'd save doing a query for it more than once. You said it was being called every time - if you meant every time the page loads, then this kind of cache won't help. You probably don't want a database cache just for all users' field16s. The best thing would be if there was already a query for userdata that you could modify to load the custom profile field values in the same query. But you couldn't do that from the fetch_musername hook - we'd have to know more about what you're doing.

I hope I haven't made things too complicated - maybe you shouldn't worry about it unless it becomes a problem.

[James Birkett]

OK, my caching is done thanks.

[Badshah93]

how many different usernames are there on that page ?
if it is more than 5-10, then you should left join userfield table with user table.