Announcement

Collapse
No announcement yet.

MySQL Indexes R-US

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    MySQL Indexes R-US

    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
    Need to offer Shipping Insurance?
    Interactive Design Solutions https://www.myids.net
    MivaMerchant Business Partner | Certified MivaMerchant Web Developer
    Competitive Rates, Custom Modules and Integrations, Store Integration
    AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
    My T-shirt Collection is mostly MivaCon T-shirts!!

    #2
    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.
    David Hubbard
    CIO
    Miva
    [email protected]
    http://www.miva.com

    Comment


      #3
      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
      Need to offer Shipping Insurance?
      Interactive Design Solutions https://www.myids.net
      MivaMerchant Business Partner | Certified MivaMerchant Web Developer
      Competitive Rates, Custom Modules and Integrations, Store Integration
      AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
      My T-shirt Collection is mostly MivaCon T-shirts!!

      Comment


        #4
        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.
        David Hubbard
        CIO
        Miva
        [email protected]
        http://www.miva.com

        Comment

        Working...
        X