[buddypress-trac] [BuddyPress Trac] #7500: Harmful bp_activity indexes

[buddypress-trac]

#7500: Harmful bp_activity indexes
--------------------------+----------------------------------
 Reporter:  brandonliles  |       Owner:  espellcaste
     Type:  enhancement   |      Status:  assigned
 Priority:  normal        |   Milestone:  Under Consideration
Component:  Performance   |     Version:
 Severity:  normal        |  Resolution:
 Keywords:  has-patch     |
--------------------------+----------------------------------
Changes (by espellcaste):

 * milestone:  Awaiting Review => Under Consideration


Comment:

 I'd like to offer a different take on the suggested approach/patch.

 The rules for composite indexes states that there are two main rules for
 using composite indexes:

 - Left-to-right, no skipping: MySQL can only access the index in order,
 starting from the leftmost column and moving to the right. It can't skip
 columns in the index.
 - Stops at the first range: MySQL stops using the index after the first
 range condition encountered.

 ^ I think that if we create this composite index as is, we might create
 other issues for different types of queries where not all keys will be
 available and a table scan will happen too.


 `component_type_user_id_date_recorded (component, type, user_id,
 date_recorded)`

 Let's look at a practical example. A default query from
 https://site.test/activity/

 {{{
 SELECT DISTINCT
         a.id
 FROM
         wp_bp_activity a
 WHERE
         a.is_spam = 0
         AND a.hide_sitewide = 0
         AND a.type NOT IN('activity_comment', 'last_activity')
 ORDER BY
         a.date_recorded DESC,
         a.id DESC
 LIMIT 0,
 21;
 }}}

 I see no index being used here.
 {{{
 #!json
 {
     "id": 1,
     "select_type": "SIMPLE",
     "table": "a",
     "partitions": null,
     "type": "ALL",
     "possible_keys": "type,is_spam,hide_sitewide",
     "key": null,
     "key_len": null,
     "ref": null,
     "rows": 115,
     "filtered": 97.39,
     "Extra": "Using where; Using filesort"
 }
 }}}

 Now, after applying the patch, the `date_recorded` index was used:

 {{{
 #!json
 {
     "id": 1,
     "select_type": "SIMPLE",
     "table": "a",
     "partitions": null,
     "type": "index",
     "possible_keys": "type,hide_sitewide",
     "key": "date_recorded",
     "key_len": "5",
     "ref": null,
     "rows": 21,
     "filtered": 9.74,
     "Extra": "Using where; Backward index scan"
 }
 }}}

 I also removed only the `is_spam` index without applying the patch and got
 the same results.


 @brandonliles I know it's been ages since you shared your feedback. But if
 we could use which index is being chosen, that would help confirm my
 findings.

 ---

 Outcome: it seems that only removing the `is_spam` index is enough to
 mysql to use another index with good cardinality, rather than a full table
 scan.

 It's not clear, based on my testing, the patch here will actually avoid
 table scan. On the contrary, it is technically possible it'll force a full
 table scan in certain situations.

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