In trying to diagnose why my site is slow, I enabled the devel query tracker. On many page loads, the slowest query is the following:

ms	#	where	query
1031.02	0	notifications_user_get_subscriptions	SELECT s.*, f.* FROM notifications s INNER JOIN notifications_fields f ON s.sid = f.sid WHERE s.uid = 1 AND event_type = 'node' AND ((f.field = 'nid' AND f.value = '118') OR (f.field = 'type' AND f.value = 'admin_node') OR (f.field = 'author' AND f.value = '1'))

This query took over a second. Many times I've seen query times between .2-.5 seconds.

I don't understand why this particular query is running on a page load -- it seems (without having dived into the your code) that it should only be run on a cron run when sending messages.

I'm willing to take the time to figure out how to diagnose the problem if given some pointers where to look but thought it'd be more effective to alert you first.

Comments

jose reyero’s picture

jose reyero commented
Category: bug » support
Priority: Normal » Critical

It runs once for every node you display, to fetch the subscriptions for the current user to that node.

To prevent it for running for each node on a node listing, disable the 'Show subscribe link with teaser' option (notifications_ui) module.

Tweaking the notifications ui options (where to show subscriptions links) may save lots of queries.

About general query performance, you are welcomed to join on this discussion: #324249: Performance issues, notifications queue query very slow

kyle_mathews’s picture

kyle_mathews commented

I'll give some feedback on the other issue thread on Monday plus provide current performance data.

One more thing here. I disabled the Notifications UI so it doesn't show anywhere but the same query is still running on each page load. That should be changed.

jose reyero’s picture

jose reyero commented

> disabled the Notifications UI so it doesn't show anywhere but the same query is still running on each page load. That should be changed.

Yes, definitely, if we can find out which is the part that triggers that query I'll take care of it.

The 'on each page load' means all pages, only node pages, maybe there's a block displaying a node, or other modules doing some node rendering? There's a bunch of options and combinations that can make it happen, including the 'Show subscribe link with teaser' and some per content type options....

kyle_mathews’s picture

kyle_mathews commented

Right, sorry. I meant by "each page load" each page load that had a node somewhere. I haven't noticed this query showing up on admin pages or elsewhere that doesn't have a node.

jose reyero’s picture

jose reyero commented
Status: Active » Postponed (maintainer needs more info)

I have no clues for this one, need more info about which event triggers all this (which is not regular node loading I think), so maybe some other module related?

kyle_mathews’s picture

kyle_mathews commented
Project: Notifications » Nofollow List
Version: 5.x-1.1 » 5.x-1.x-dev
Status: Postponed (maintainer needs more info) » Closed (fixed)

Jose - I've marked this issue closed. I haven't seen any unusual trouble from notifications for quite awhile. I'm thinking this issue was fixed by other performance related fixes you've made.

Thanks for checking back.