MySQL Indexes R-US

[ids]

I have a question for an index issue with preventing duplicates on certain columns. Because it's very similar, I've tried using the scheme Miva uses for Attributes and Options. I'm not getting it to work.

What I need to accomplish is very similar to the Attribute and Option Tables. Setting a Unique Index on each table is easy enough for the ID but the CODE for each table cannot be duplicated with the exception of the codes in Options table where the attr_id (ID from the attributes table) can have the CODE duplicated IF the attr_id is different. Therefore,
attr_id and opt_id, code,
1 and 1 = red
1 and 2 = blue

2 and 3 = red
2 and 4 = blue

In addition, I have a display order type of column in options where the display order must be unique. (atm, not trying to rest any option set to start at 1.

I'm asking for a fresh strategy I guess. Thanks for any suggestions.

Scott

[ILoveHostasaurus]

I would recommend against altering/creating indexes on any core Miva Merchant database tables. Such a change can result in store failure (i.e. down until repaired) during patching / upgrades because there are certain scenarios the upgrade process cannot detect or predict. The issue is that certain types of indexes, or indexes across various columns, cannot always coexist with an index Miva Merchant may attempt to create in the future. If you're running into a problem that needs an index to solve, I'd suggest reaching out to our software developers first to discuss alternatives, and/or see if the issue would require core software changes.

[ids]

I guess I wasn't perfectly clear when I mentioned it was "similar." This is a custom module where the data set is very similar to Attribute and Options tables in operation. My mention/usage is my attempt to describe how I need the user interface and queries to work.

Scott

[ILoveHostasaurus]

Ah okay. Setting a index type of unique on the relevant colum(s) would prevent duplicates, but if you're not otherwise checking that you are not issuing queries which would cause a duplicate, your query would end in error due to the index, so you'd then need error detection to avoid a Miva Merchant fatal error when it's executing the query in question. You can test such queries in MySQL or use its EXPLAIN function to determine which indexes apply to your query, and how, including whether they're benefiting the query or it's still looking at more rows than it ideally would.