What causes Duplicate KEY Indexes?

[Dirty Butter]

What action on my part or CC's part causes all these duplicate KEY Indexes?

[bsmither]

MySQL allows for the creation of multiple indexes on the same column -- that is, column set, sort order, or other conditions, per table -- but they must have different names. Hopefully, each index will have different conditions against the columns.

CubeCart creates indexes using these syntaxs:

1. PRIMARY KEY `admin_id` (`admin_id`)
2. PRIMARY KEY (`log_id`),
3. PRIMARY KEY (`group_id`),
4. KEY `type` (`type`),
5. KEY `time` (`time`)
6. KEY (`group_name`)

Note that 1, 4, and 5 include a name for the index (usually, for CubeCart, the key name is the same as the column name).

But a name is not absolutely necessary, as seen in 2, 3, and 6.

When a name is not provided, it is understood that the index name will be the column name.

However, in this missing index name situation, when an index name already exists for a given column, the database server will append a _# to the new index name, such as 'group_name_1'. Executing the same SQL statement more than once, using the syntax that does not include an index name, will result in multiple indexes pointing to the same column.

CubeCart is not coded to use any index it does not expect, so these extra indexes will not cause a problem, per se, but it does give the database server extra work to do.

CubeCart's database diagnostics page will detect and report duplicate KEY indexes when the same column (with conditions) is being seen associated with more than one index - regardless of name.

[Dirty Butter]

I understood that these extras were not harming the CC function. But I would like to understand why these are created?

I play around with test sites and sometimes can't remember if I have already run the Setup queries for a certain version yet or not - particularly when I am playing with commits within one version. Is THAT what may be causing duplicate keys?

 

[bsmither]

Very likely.