[buddypress-trac] [BuddyPress Trac] #5572: last_activity migration function is unnecessarily slow

[buddypress-trac]

#5572: last_activity migration function is unnecessarily slow
--------------------------+--------------------
 Reporter:  boonebgorges  |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  2.0.1
Component:  Core          |     Version:
 Severity:  normal        |  Resolution:
 Keywords:                |
--------------------------+--------------------

Comment (by r-a-y):

 So I tried playing around with MySQL stored procedures to make the DELETE
 query scale better.

 Here's what I used in the MySQL command-line:

 {{{
 mysql> delimiter //
 mysql> CREATE PROCEDURE bp_delete_user_last_activity()
     -> MODIFIES SQL DATA
     -> BEGIN
     -> REPEAT
     -> DELETE FROM wp_bp_activity
     -> WHERE component = 'members' AND type = 'last_activity'
     -> LIMIT 1000;
     -> UNTIL ROW_COUNT() = 0 END REPEAT;
     -> END //
 Query OK, 0 rows affected (0.00 sec)

 mysql> delimiter ;
 mysql> CALL bp_delete_user_last_activity();
 Query OK, 0 rows affected (0.55 sec)
 }}}

 0.55 seconds to purge the last activity on a 5000 user install.  Not too
 shabby.  (Btw, wp-cli-buddypress kicks ass!)

 Then, I tried to translate this over to using `$wpdb->query()`.

 Apparently, `$wpdb->query()` doesn't seem to like stored procedures.  A
 user on StackExchange encountered a similar problem and worked around this
 by using mysqli:
 https://wordpress.stackexchange.com/questions/127344/wpdb-and-mysql-
 create-trigger

 However, this is probably not something we can use since not all setups
 will have the mysqli extension available.

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/5572#comment:2>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac