Project

General

Profile

Bug #10680

wp_bp_activity table has extra 'component' index

Added by Boone Gorges 3 months ago.

Status:
Resolved
Priority name:
Normal
Assignee:
-
Category name:
Server
Target version:
Start date:
2018-11-08
Due date:
% Done:

0%

Estimated time:

Description

Looking into some other issues, I see that the wp_bp_activity table has a component_name index (which is provided by BP) as well as a redundant, apparently unused 'component' index. I'm going remove the latter. This ticket is to document the change.

Before:

mysql> show index from wp_bp_activity;
+----------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name          | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_bp_activity |          0 | PRIMARY           |            1 | id                | A         |      178192 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | date_cached       |            1 | date_cached       | A         |        5399 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | date_recorded     |            1 | date_recorded     | A         |      178192 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | is_private        |            1 | is_private        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | user_id           |            1 | user_id           | A         |       22274 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | item_id           |            1 | item_id           | A         |       35638 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | component_name    |            1 | component         | A         |          34 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | secondary_item_id |            1 | secondary_item_id | A         |       59397 |     NULL | NULL   | YES  | BTREE      |         |               |
| wp_bp_activity |          1 | component         |            1 | component         | A         |          34 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | type              |            1 | type              | A         |          64 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | mptt_left         |            1 | mptt_left         | A         |          60 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | mptt_right        |            1 | mptt_right        | A         |          68 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | hide_sitewide     |            1 | hide_sitewide     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| wp_bp_activity |          1 | is_spam           |            1 | is_spam           | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)

After:

mysql> alter table wp_bp_activity drop index component;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from wp_bp_activity;
+----------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name          | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_bp_activity |          0 | PRIMARY           |            1 | id                | A         |      178164 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | date_cached       |            1 | date_cached       | A         |        5398 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | date_recorded     |            1 | date_recorded     | A         |      178164 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | is_private        |            1 | is_private        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | user_id           |            1 | user_id           | A         |       22270 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | item_id           |            1 | item_id           | A         |       35632 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | component_name    |            1 | component         | A         |          34 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | secondary_item_id |            1 | secondary_item_id | A         |       59388 |     NULL | NULL   | YES  | BTREE      |         |               |
| wp_bp_activity |          1 | type              |            1 | type              | A         |          64 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | mptt_left         |            1 | mptt_left         | A         |          60 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | mptt_right        |            1 | mptt_right        | A         |          68 |     NULL | NULL   |      | BTREE      |         |               |
| wp_bp_activity |          1 | hide_sitewide     |            1 | hide_sitewide     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| wp_bp_activity |          1 | is_spam           |            1 | is_spam           | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.00 sec)

Also available in: Atom PDF