This query is taking 35 minutes on a site with 65k rows in heartbeat_activity and 180k rows in og_ancestry.


+----+-------------+-----------------+--------+---------------------------------------+------------+---------+---------------------------+--------+------------------------------------------------+
| id | select_type | table           | type   | possible_keys                         | key        | key_len | ref                       | rows   | Extra                                          |
+----+-------------+-----------------+--------+---------------------------------------+------------+---------+---------------------------+--------+------------------------------------------------+
|  1 | SIMPLE      | ua              | ref    | timestamp,uid,uid_target,nid,language | language   | 39      | const                     |  38057 | Using where; Using temporary; Using filesort   | 
|  1 | SIMPLE      | hm              | ref    | message_id                            | message_id | 753     | example.ua.message_id |      1 |                                                | 
|  1 | SIMPLE      | ua_users        | eq_ref | PRIMARY                               | PRIMARY    | 4       | example.ua.uid        |      1 | Using where                                    | 
|  1 | SIMPLE      | ua_target_users | eq_ref | PRIMARY                               | PRIMARY    | 4       | example.ua.uid_target |      1 | Using where                                    | 
|  1 | SIMPLE      | oa              | ALL    | PRIMARY                               | NULL       | NULL    | NULL                      | 120219 | Range checked for each record (index map: 0x1) | 
+----+-------------+-----------------+--------+---------------------------------------+------------+---------+---------------------------+--------+------------------------------------------------+
5 rows in set (0.00 sec)

Removing the join on og_ancestry gets it down to 0.6 seconds.

There is still a filesort on the heartbeat_activity table, but that can be fixed with ALTER TABLE heartbeat_activity ADD INDEX language_timestamp (language, timestamp);

To replace the join on og_ancestry I'm thinking it'd be possible for og_activity to add a column to heartbeat_activity - adding a 'in_group' flag to the table - this would be evaluated in PHP before adding records, then it's just an extra condition on that table rather than the join + range.

Will be a couple of days before I'm able to work on this most likely but wanted to get the issue open.

CommentFileSizeAuthor
#7 heartbeat_og_1267500.patch4.24 KBcatch
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Stalski’s picture

Stalski CreditAttribution: Stalski commented

Could it be you are using drupal6? I have not written an implementation for og activity within heartbeat yet. I was even wondering if that's needed, since in drupal7 the views approach provides enough features.

Or are you creating a custom module that provides this integration? In that case, great!

- Edit - Actually it must be drupal6 since {heartbeat_messages} does not exist anymore (taken over by ctools export api)

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented
Version: 7.x-1.x-dev » 6.x-4.x-dev

Sorry this is Drupal 6, I posted it against 7.x-1.x without looking in the branch out of habit.

Stalski’s picture

Stalski CreditAttribution: Stalski commented
Status: Active » Needs work

The change is actually not that big. It's certainly something to fix.

Stalski’s picture

Stalski CreditAttribution: Stalski commented

The change is actually not that big. It's certainly something to fix.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented
Assigned: Unassigned » catch

I'm working on this.

Stalski’s picture

Stalski CreditAttribution: Stalski commented

There is an issue to port this functionality to D7, so the moment you do and it gets accepted, I'll take time to port this as a drupal7 submodule.
Thx catch

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented
Status: Needs work » Needs review
FileSize
heartbeat_og_1267500.patch4.24 KB

OK here's the patch:

og_activity does this:

hook_schema_alter() the new in_group column
Add index for in_group, language, timestamp.
hook_heartbeat_activity_log() to add the in_group value to log items.

I also added an update to set this for existing records.

Tested both the update and creating a node in a group. Seems to be working OK.

Stalski’s picture

Stalski CreditAttribution: Stalski commented
Status: Needs review » Fixed

Awesome. Thx a lot.
Tested and works great. This is pushed to git already.

Stalski’s picture

Stalski CreditAttribution: Stalski commented

- Added the in_group where clause in the query used when selecting "groupsactivity" in views UI.
- Fixed og_activity_heartbeat_activity_log so it takes existing "nid"

Stalski’s picture

Stalski CreditAttribution: Stalski commented

Saw another issue. Code currently:

/**
 * Implementation of hook_heartbeat_activity_log().
 */
function og_activity_heartbeat_activity_log($heartbeatactivity, $args) {
  $in_group = (int) db_result(db_query_range("SELECT 1 FROM {og_ancestry} WHERE (nid = %d OR group_nid = %d)", $heartbeatactivity->nid, $heartbeatactivity->nid, 0, 1));
  db_query("UPDATE {heartbeat_activity} SET in_group = 1 WHERE nid = %d OR nid_target = %d ", $heartbeatactivity->nid, $heartbeatactivity->nid);
}

Seems to be working as expected now.

Stalski’s picture

Stalski CreditAttribution: Stalski commented

Hmm after some more extending testing, it did not work properly.
This is a the new code. A quick peek review would be great.

/**
 * Implementation of hook_heartbeat_activity_log().
 */
function og_activity_heartbeat_activity_log($heartbeatactivity, $args) {

  // Most of the time nid is a group post and nid_target the group, sometimes nid_target left empty.
  // In some cases the nid is the group id, leaving the nid_target empty.
  $in_group = (int) db_result(db_query_range("SELECT 1 FROM {og_ancestry} WHERE (nid = %d OR group_nid = %d OR group_nid = %d)", $heartbeatactivity->nid, $heartbeatactivity->nid, $heartbeatactivity->nid_target, 0, 1));
  if ($in_group) {
    db_query("UPDATE {heartbeat_activity} SET in_group = 1 WHERE uaid = %d ", $heartbeatactivity->uaid);

  }

}
catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented

any reason you're not checking nid against nid_target?

Stalski’s picture

Stalski CreditAttribution: Stalski commented

Hehe, indeed, that did not work either.
It works in all cases and languages now (with uaid it worked only for last inserted record).

/**
 * Implementation of hook_heartbeat_activity_log().
 */
function og_activity_heartbeat_activity_log($heartbeatactivity, $args) {

  // Most of the time nid is a group post and nid_target the group, sometimes nid_target left empty.
  // In some cases the nid is the group id, leaving the nid_target empty.
  $in_group = (int) db_result(db_query_range("SELECT 1 FROM {og_ancestry} WHERE (nid = %d OR group_nid = %d OR group_nid = %d)", $heartbeatactivity->nid, $heartbeatactivity->nid, $heartbeatactivity->nid_target, 0, 1));
  if ($in_group) {
    db_query("UPDATE {heartbeat_activity} SET in_group = 1 WHERE nid = %d AND nid_target = %d ", $heartbeatactivity->nid, $heartbeatactivity->nid_target);

  }

}

In previous patches , nothing was being done with $in_group. So this seems to work and looks Ok to me.

Automatically closed -- issue fixed for 2 weeks with no activity.