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.