A UNIQUE INDEX must include all columns in the table's partitioning function

From Kolmisoft Wiki
Jump to navigationJump to search

When running Percona upgrade.

show indexes from calls;

Bad structure with repeating indexes:

mysql>  show indexes from calls;
+-------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| calls |          0 | PRIMARY           |            1 | id            | A         |        7138 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          0 | id                |            1 | id            | A         |        7331 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          0 | id_2              |            1 | id            | A         |        7331 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          0 | id_3              |            1 | id            | A         |        7331 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          1 | src_device_id     |            1 | src_device_id | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          1 | dst_device_id     |            1 | dst_device_id | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          1 | src               |            1 | src           | A         |        3658 |     NULL | NULL   |      | BTREE      |         |               |
| calls |          1 | src               |            2 | disposition   | A         |        4068 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | provider_id       |            1 | provider_id   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | card_id           |            1 | card_id       | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | disposition       |            1 | disposition   | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | user_id_index     |            1 | user_id       | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | hgcause           |            1 | hangupcause   | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | resellerid        |            1 | reseller_id   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | dst_user_id_index |            1 | dst_user_id   | A         |         153 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | did_id            |            1 | did_id        | A         |         154 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | uniqueidindex     |            1 | uniqueid      | A         |         370 |        6 | NULL   |      | BTREE      |         |               |
| calls |          1 | calldateindex     |            1 | calldate      | A         |        7270 |     NULL | NULL   | YES  | BTREE      |         |               |
| calls |          1 | dateindex         |            1 | date          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 rows in set (0,00 sec)

Delete indexes that are not necessary:

ALTER TABLE calls DROP INDEX id;
ALTER TABLE calls DROP INDEX id_2;
ALTER TABLE calls DROP INDEX id_3;

Rerun Percona.