Jump to content
Sign in to follow this  
Dirty Butter

What causes Duplicate KEY Indexes?

Recommended Posts

Posted (edited)

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.

Edited by bsmither

Share this post


Link to post
Share on other sites

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?

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...