Announcement

Collapse
No announcement yet.

MySQL CREATE INDEX

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

    MySQL CREATE INDEX

    I need to add the index back in for 1 table. Where it may or may not exist.

    I'm updating an old module where at some point in time the developer removed all indexes from the database modules. This means in the wild, some versions exist with tables and indexes, and some do not.

    Can I just do the following and ignore any resulting error, or is there a better way?

    Code:
    <MIVA MvQUERY_Error = "nonfatal, nodisplay">
    <MvQUERY NAME = "Merchant"
                 QUERY = "{ 'CREATE INDEX ' ....
    Ray Yates
    "If I have seen further, it is by standing on the shoulders of giants."
    --- Sir Isaac Newton

    #2
    Try this:
    Code:
          <MvQuery name="newtables" QUERY="{ 'ALTER TABLE tableName ADD UNIQUE (`orderNum`);' }">
    William Gilligan - Orange Marmalade, Inc.
    www.OrangeMarmaladeinc.com

    Comment


      #3
      Thanks Mr G. What will this do if the index already exists?
      Ray Yates
      "If I have seen further, it is by standing on the shoulders of giants."
      --- Sir Isaac Newton

      Comment


        #4
        Solution: Ignore errors containing the word Duplicate ( e.g. "Duplicate key name 's01_tablename_1' )

        Code:
        <MvQUERY NAME = "Merchant"
                     QUERY = "{ 'ALTER TABLE ' $ g.Store_Table_Prefix $ 'tablename ADD KEY ' $ g.Store_Table_Prefix $ 'tablename_1 (basket_id)' }">
            <MvIF EXPR = "{ g.MvQUERY_Error }">
                <MvIF EXPR="{ NOT ('Duplicate' CIN g.MvQUERY_Error) }">
                    <MvFUNCTIONRETURN VALUE = "{ 'Unable to create index tablename_1: ' $ g.MvQUERY_Error }">
                </MvIF>
            </MvIF>
        Ray Yates
        "If I have seen further, it is by standing on the shoulders of giants."
        --- Sir Isaac Newton

        Comment

        Working...
        X