Difference between revisions of "A UNIQUE INDEX must include all columns in the table's partitioning function"
From Kolmisoft Wiki
Jump to navigationJump to search
(Created page with "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 | +-------+------------+-------------------...") |
(No difference)
|
Revision as of 13:44, 22 July 2022
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; ALTER TABLE calls DROP INDEX calldateindex; ALTER TABLE calls DROP INDEX dateindex;
