Optimize performance of _user_stats_last_post() [#829134]

You could do several things to make _user_stats_last_post faster

First:

use db_query_range($sql, 0, 1) instead of db_query, this might be faster

472   $max_node = db_result(db_query($sql, 1, $uid));
473   $sql  = "SELECT MAX(timestamp) FROM {comments} c
474     INNER JOIN {node} n ON c.nid=n.nid
475     WHERE c.status=%d AND c.uid=%d";
476   if (!empty($post_count_content_types)) {
477     $where = ' AND n.type IN ('. $content_types .')';
478     $sql .= $where;
479   }

Only join if $post_count_content_types is not empty.

Comment	File	Size	Author
#8	829134-performance-2.patch	1.98 KB	Liam McDermott
#2	829134-performance.patch	1.14 KB	dawehner

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

Witch CreditAttribution: Witch commented 11 July 2010 at 11:31

Hi dereine,

i am interested in your perfomance suggestions. Did you patched the module and may you post a patch or the patched version?

Thank you.

Comment #2

dawehner

German

CreditAttribution: dawehner commented 11 July 2010 at 12:20

Status:

Active

» Needs review

File	Size
829134-performance.patch	1.14 KB

here is one. This patch is not tested, so someone should do this :)

Comment #3

dawehner

German

CreditAttribution: dawehner commented 12 November 2010 at 14:01

PS: This patch works fine here.

Comment #4

Liam McDermott CreditAttribution: Liam McDermott commented 12 November 2010 at 15:23

Thanks for bumping this, I meant to look at it ages ago!

Looks good, I should be able to test and commit it tomorrow.

Comment #5

introfini CreditAttribution: introfini commented 26 January 2011 at 18:34

Can this please be committed . It's working for me too.

Comment #6

Liam McDermott CreditAttribution: Liam McDermott commented 5 February 2011 at 04:34

Sorry for still not getting to this, have been working remotely and haven't had a chance to setup a local test install with Xdebug and all that good stuff.

Before applying the patch I thought I'd have a look at using db_query_range instead of db_query, unfortunately it's slightly slower.

Am expecting the patch is going to make things faster, however. Hopefully, I actually will get to look at the patch tomorrow. Thanks for being patient. :)

Comment #7

Liam McDermott CreditAttribution: Liam McDermott commented 5 February 2011 at 15:34

Status:

Needs review

» Needs work

The patch doesn't work if you have a content type selected on the User Stats configuration page. It tries to add two WHERE clauses to the query:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node n ON c.nid=n.nid WHERE c.status=0 AND c.uid=0 AND n.type IN ('f' at line 1 query: SELECT MAX(timestamp) FROM comments c WHERE c.status=0 AND c.uid=38 AND n.type IN ('forum') INNER JOIN node n ON c.nid=n.nid WHERE c.status=0 AND c.uid=0 AND n.type IN ('forum') in /var/aegir/platforms/drupal-6.20/sites/vbtodrupal.dyn.intermedia-online.com/modules/user_stats/user_stats.module on line 477.

I'm going to think of a way to fix this, then test the performance of the patch.

Comment #8

Liam McDermott CreditAttribution: Liam McDermott commented 5 February 2011 at 21:10

Title:

Optimize performance of _user_stats_last_post()

» Optimize performance of _user_stats_last_post

File	Size
829134-performance-2.patch	1.98 KB

Attached is the patch that's being committed. I fixed the problem with the WHERE clause being added twice and added some logic to also exclude the JOIN when all node types are selected on the config page (since it's logically equivalent to none).

Note the call to node_get_types(), I checked and this caches its results and is called earlier in the page, so the cost is almost nothing.

** EDIT **
Forgot to mention: testing showed about a 1% - 2% saving in page load times. Doesn't seem like much, but will soon add up. Thanks very much for the patch!